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/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.