Combining data from daily files to calculate average and max of UIDs

Question

Hi-

there are reports created daily, stored in the same folder with a similar name, only the date changes.

I am looking to have a workflow that combines the daily documents and averages the values in the 1st, 2nd, and 3rd columns if the bucket title matches. See example of two reports below and the export i am wanting.

Just example as there are 100s of daily files I am looking to combine and find the average of buckets from

Daily Report - YYYY-MM-02.xlsx

daily report average - 2024-08-13 example output.xlsx

Daily Report - YYYY-MM-01.xlsx

griffinwelsh · Answer

@Ksisterhen you will need to use a batch macro to accomplish this efficiently. Are you wanting one column for each day and the aggregates? Also are you wanting to identify the files by created date or filename date string?

Ksisterhen · Answer

Thank you!

One other item i forgot to ask for, i am wanting to average the numbers from prior month and specific days, i/e today i want to see what the average was for the buckets from files dated the 13th of the prior months, i.e 7-13, 6-13, 5-13, etc, how can i add that in?

griffinwelsh · Answer

@Ksisterhen updated workflow meeting your new requests is attached.

1. You can use a formula tool to append the sheet name to the fullpath. The format will be fullpath.xlsx|||sheetname

2. You did not specify the time period in your initial post. This was just an example to show how to use a filter to pick specific days.

3. This needed to be updated in the dynamic input to skip the headers and just pull in columns by position.

New Workflow1.yxzp