Flow to find and add duplicates in excel for all time usage report

[CROKat]

My team has to pull usage metrics from a SharePoint site every week for popular content. Currently, my teammate pulls the excel report, combines it with the metrics excel that contains the all-time popular content list, sorts alphabetically, finds the duplicate line items, adds the total unique viewers and visits to the existing popular content total, and then delete the duplicate line item. It takes over an hour and feels very cumbersome.

Is there a flow that can be created to automatically sort and find duplicates and add the totals together? Or would this be a simple (lol) excel formula instead? Or if there is a better way to get an all-time popular content usage, I'd love to hear your thoughts.

[FLMike]

Hi @CROKat 

So I have no doubts my colleagues will have other variations to do this, but essentially if I understand it, you want to pair up any duplicates, take the new row and add those values (or overwrite, whichever) to the existing.

So essentially Row 5 is for XYZ and Row 47 is also XYZ

You want to utilize data from Row 47 to enhance Row 5 and then delete 47

I don't know how many rows you have???? So please take that into account for my answer, as I don't see the data, so it's makes it much more difficult to help you.

Let's assume you have a Column that you use to identify everything. I don't know lets pretend its the URL

Pre-Steps

-Make sure that your Master Excel (which has all the NON-Duplicates) is in SharePoint or Onedrive

-Make sure that you have an Excel Table around the data in the Master

-Make sure you have a Column Key

1. Create a Scheduled Flow, to run whenever it needs to run

2. Do a Get Items to grab the Data from Excel, that you want to add/enhance the existing Master file with

3. Add a Condition

In the left put length(InsertDynamicGetItemsBody/Valuehere)

In the middle put is greater than

right side put 0

4. In the Yes side (ignore the No side as you don't care about that part)

Put a Get a Row (Excel Action)

You will use the data from that Loops Get Items, to populate the Column Key

5. Right after put a condition

Verify that Get a Row returned a row

-If it did, then update it with the data that is in the Get Items loop

-If it didn't find one, then add the row to the excel.

So overall you are

Looping through the Items in Sharepoint

Querying Excel to see if an existing row exists

If it does update it

if not add a row

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