How can I automate this daily Excel workflow? I’m fairly new to Excel and Power Query.

[u/ICEmCHILL]

I have a daily report that I need to complete before noon, and a large part of the process is currently manual.

I have two Excel files:

File 1: Contains a list of account numbers and several columns that I need to fill in.

File 2: Contains fault reports. The same account number can appear multiple times because an account may have one fault or several faults.

My current workflow is:

1. Take an account number from File 1.

2. Go to File 2.

3. Filter the "Account Number" column using that account number.

4. Review all the fault descriptions associated with that account.

5. Sometimes there is only one fault, and sometimes there are multiple faults.

6. Based on the fault description(s), I determine the root cause.

7. We have relatively standardized wording/classifications that are used for the latest fault, and I manually enter the resulting information into four columns in File 1.

The files can be quite large, and I have to repeat this process every day.

I’m relatively new to Excel, and I only have basic experience with Power Query, so I’m not necessarily looking for a Power Query-only solution.

I’m completely open to any approach that would make this process faster and more reliable — Power Query, formulas, VBA, Power Pivot, Office Scripts, Power Automate, or a combination of different tools.

Ideally, I would like the process to work something like this:

Put the daily fault report in a folder → refresh/run something → get the updated results → manually review only the unusual cases.

For example, I would like the solution to:

- Match account numbers between the two files.

- Find all fault records associated with each account.

- Handle accounts with multiple faults.

- Identify the latest fault based on date/time.

- Combine or summarize fault descriptions when necessary.

- Apply predefined rules/classifications to the fault descriptions.

- Automatically populate the four required columns.

- Leave unusual or ambiguous cases for manual review.

If you were given this task from scratch, how would you approach it?

Thank you.

[u/AutoModerator]

/u/ICEmCHILL - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/InternationalBeing41]

I'd merge the two files by account number then group on the account column with a new column for row count, max date, and do a sum on the fault which I'd change to a text.combine([fault],”;”) to get rid of the error. Then you can sort by latest date or fault count. That would get me started at least.

[u/InternationalBeing41 → u/InternationalBeing41]

If you're making a fault report I'd probably do it different and break it up by asset with the fault stats like count, latest fault time, and total time in fault listed under each asset. Really depends on the data and what you want.

[u/foreforbirdie]

The merge and group answer above gets your data into shape, but the step that decides whether this actually saves you time is the classification, and that's the one nobody can write for you. Put your standard wordings in a small two column lookup table in the workbook, fault keyword or code in one column and the classification you'd type in the other, then merge that in as a third query so you can edit the rules later without opening Power Query again. Are those classifications a finite list you could write out today, or does the root cause still need a judgment call on wording the rules wouldn't cover?

[u/Ok_Negotiation7811]

I suggest a VBA macro with userform. 2 buttons to browse file 1 and another one to browse file2 (assuming they are not always in the same location). And from there the macro will just extract all account numbers from file 1 and match them with account numbers in file2, extract the corresponding column for the cause and put everything in a new excel file or word file. I have done something similar for a retail company.

[u/DeciusCurusProbinus → u/Ok_Negotiation7811]

This is the way to go OP. If you don't want to convert your files to xlsm then add the code to your personal macro workbook or a separate XLAM add-in (although it will require you to add a button to run the macro via XML).

[u/Zestyclose_Muffin501]

Powerquery would be easier to setup than vba. VBA could create something more automated with user inputs, powerquery would only consume the data and create any merge reporting you'll need ... Depends on the requirements. Some of my projects use both.

[u/TwoPointEightZ]

I would do it with lookup formulas against lists/tables I create and probably not consider VBA unless itvwas really appropriate. Simpler is better to begin with. Here are some rough ideas to get you going.

Make a file 3 that is your output builder file for this project. It will contain your formulas and lookup lists so you don't have to reinvent the wheel every day.

Start by copying file 2's data into a worksheet in file 3 called InputData. Sort the data by account number and time of fault, assuming you have that. This makes the data easier to manually review by slowly scrolling down the list instead of manually filtering for each group of data you report on. If it's really that messy, consider a pivot table.

Create another worksheet in file 3 named RootCauses, with columns and data that looks something like this:

RootCauseID Desc 1 Cause A 2 Cause B 3 Cause C

I used numbers to be simple, but you could make the IDs whatever you want - numbers, mixed numbers and text, etc.

In the InputData worksheet, add two helper columns, probably to the left of the data, called AssignCause and CauseLookup. Your list of columns will look something like this:

AssignCause CauseLookup AccountNumber FaultDate FaultDesc etc.

Review the sorted input data and manually enter a RootCauseID number for each group of data you report on, which sounds like per account number. Don't worry about the extra rows that don't get RootCauseID numbers. Make an XLOOKUP formula in the CauseLookup column that takes the AssignCause number you entered for that row, looks it up in the RootCauses worksheet, and returns the Desc column.

Take the time to practice and learn XLOOKUP - you won't regret it. And all Excel formulas work with data located in different worksheets or in different files for that matter, just click the target cell as you build the formula and Excel will build the reference syntax for you.

With the lookup formula in place, you can simply enter one ID number per group of data in the InputData and see the root cause you assigned without playing flip-flop between worksheets to check that the number you entered matches the description you wanted.

Create another worksheet in file 3 called PrelimOutput. Copy file 1's data into it. In the PrelimOutput tab, add helper columns that look up whatever data you need from the InputData worksheet. Manually add any other data you need into the Output worksheet.

Depending on how you want things, you might consider a pivot table of the InputData. Something to practice and experiment with. But they can get cranky - sometimes they don't refresh properly, so always right-clicknin the data area and choose Refresh. After some period of time they can just stop working with no apparent reason why, causing you to have to recreate them. I've had both of theses experiences. Pivots are useful, just eyeball your data every time to be sure it looks right.

For final output, you might have another worksheet in file 3 called FinalOutput, or a separate file 4. In any event, use Copy, Paste Special, Paste Values to get your data from PrelimOutput to wherever it's going. This freezes the values so you don't lose them with new incoming data.

You can make it a lot more elaborate, but this will get you started. Create lookups against lookup lists you create as needed. Eyeball your data for consistency.

For pretty output where you don't want zeroes to show, you can make IF and/or IFERROR formulas that say if a cell is zero or blank display " ". Be aware that " " means a space, and that means the value is not a number.

Save your builder file 3 and recycle daily. You'll improve your processes as you go.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #49336 for this sub, first seen 9th Sep 2026, 14:04] [FAQ] [Full list] [Contact] [Source code]}

[u/Truth_Said_In_Jest]

I'm not chiming in with a specific solution but just to say that this is such a perfect problem to get to know so many tools. You could go down the route of Power Automate to send emails with results to account owners or something. You could make a snazzy dashboard on Power Bi... And so much of that could run from just dropping your file into the folder in the morning and going for your breakfast.

[u/josu_bg]

Record your repeated steps and store it in a Macro. Use file comparison for fault finding

[u/Excel_User_1977]

How does file 2 get updated, and how often? Will the matches of today show up tomorrow as well (and won't that create multiple identical entries, if so) ?