Flow to create consolidate different Excel Spreadsheets into one

[karimckamel]

Hi everyone,

I have about 5 Excel files that have the same columns and were filled by different people with activities and dates.

What is the best way to consolidate all the rows from those files into one spreadsheet?

I need that to update for every new row created or existing rows that are changed on the separate files.

Can anyone help?

[FLMike · Accepted solution]

Hi @karimckamel 

Let's first start with part one.

To do what you want, you have to use the Create Table Action for Excel to put a table around the rows in each Excel Spreadsheet that you want to read data from. This assumes that they don't already have that.

Once you have that, you can then create another spreadsheet or have one that you plan to keep updating, and make sure it also has a table in it over top of the rows that have data (or no data possibly at first)

Then iterate through each Spreadsheet and insert the rows into the Consolidated spreadsheet. I don't know if you care of duplicates, or also having like an owner column or something to specify which person created the data you are now reading

Now for part 2

Now you say the word spreadsheet, but you seem to mean different workbooks that have spreadsheets in them. Either way it's the same thing I said above.

NOTE: I have no idea how you keep track of the 5ish files and the names of the spreadsheets as well as how is all this work is triggered. 

So you need to iron those details too

But for adding or updating

You would do the first section, but instead of simply inserting the row, you would have to do a Get Items on the Excel master consolidated spreadsheet to see if a record already exists and if so, update it depending on if its changed or if it doesnt even exist, add it.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey