Macro for Combine multiple excel files from a folder into one removing duplicates

Hi,

I have around 30+ files with same 3 columns that needs to be merged after removing duplicates using a macro rather than pulling up up all files in workflow canvas and combining them using Union tool.

Is there a way to do this using Dynamic input tool or batch macro?

I have attached my current workflow created for reference. please help.

FCO WORKFLOW.yxmd

Macros

Accepted answers

Lakshmi11

Thank you so much for the solution

All comments

BrandonB

Absolutely! You can use a directory tool to pull all of the file names from the folder that you want to bring them in from, then feed that into a dynamic input tool using one of the files as a template and reading in the Full Path as the data field and "Change Entire File Path" in the action setting. This will effectively pull all of the files from that folder together into one stacked data source.

Edit: You will also want to use a formula tool to update the file path with the sheet name just prior to the dynamic input tool.

Formula with FileName as the output column

[FileName] + "|||VincentReport$"

This way it will always pull that sheet name from the files being brought in

Lakshmi11

Thank you so much for the solution

sangpom33

I could not get this to work. It give me the error "No Sheet specified, you must specify a sheet" and i did specify the sheet in the Formula tool as well as the "Input Data Source Template"

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235