Append the data from multiple Excel files which have the similar names to a Single File

Question

Hi Team, I would like to append the data from multiple Excel files which have similar names and formats into a single file (in one single sheet one after the other).

I would need to evaluate the total of all the transactions matching with the total provided in the same sheet and then if the SUM of the transactions and the TOTAL provided are matching, only then the file needs to be considered and appended to the Specific Excel File in a Single Sheet and save the file as a Consolidated file.

Attached are the screenshots of the "Files with similar names" & "Sheet Data Formats and Totals" for your reference.

Now I would like to create one single workflow to automatically

1. Pick the Excel Files with Similar Names

2. Validate and Cross-check the Totals provided in the Sheet Vs the Sum of all the Transactions

3. If both are matched, then append the data from that file to the New Excel File with the Similar Name_Consolidated

4. The same process needs to be repeated for all the other similar Excel Files and finally save the file in the specific location.

I don't know whether a Batch Macro can be used or the Analytic App to perform this process.

Thank you for your help in advance.

Files with Similar Names.png

Excel File Formats with Totals.png

ed_hayter · Answer

This is a bit strange looks like the macro never arrived. I have atatched it here - probably wont work with my inputs due to relative file paths but you can open it up see what its doing and perhaps replicate it for your own data? the set up for the macro is just dragging the directory tool to both anchors and in the macro configuration make sure group by and control parameter is set to the filepath

Batch Macro - Cross Check Total Then Union.yxmc

Rahulkanth · Answer

Hi @ed_hayter

I have tried the file which you have attached, however, I'm getting an error and I have attached that image for your reference. Also, it is taking too long to import and load, don't know the reason. Please help!

Error.png

ed_hayter · Answer

Looks like this could be done with dynamic input. Select Record might be an approach to pulling the grand totals from each total to then crossvalidate. A complication might arise from the different files having different structures.

I have wrapped it in a batch macro so that each file path goes through the parsing flow at a time before unioning on if the total sales = the total sales listed in the data. I have mocked up a simple example but should transfer in principle to yours so feel free to explore.

Directory of Excel Files CrossCheck Total and Union.yxzp