Excel to Excel

How do I take a lot of small Excel spreadsheets and make one Excel spreadsheet without duplicates but keeping any additional information that another excel sheet would have.

You have to use a little trick. Follow these steps below.

1. Create a new definition to export all data into a new excel workbook .
2. Append (manually) all excel spreadsheet to this excel workbook by using the little trick see the forum post " Excel append records "
3. Create another definition select the excel workbook you have created above.
4. Move the Sort Heater into the definition and connect the certain field where contains the duplicate information to the Sort heater .
5. Move the GroupBy Heater into the definition and connect the same field to the GroupBy Heater. This removes duplicate records on the WRITE side.

NOTE: You can automate step 2 by using the FHBatch module. Have a look to the Example: " How to import several CSV text files automatically to an Access database ".

I am having trouble with this. does it work for spreadsheets with names and addresses?

We could make an example for you, what is your problem? Sorting and grouping to filter the duplicate rows/records or appending all your small excel spreadsheets first to one big excel workbook?

Please give as a bit more information, thanks.

My biggest problem is taking these names with all different and some same information and consolidating them to one instance. I ended up just making new masters and copying and pasting to one massive spreadsheet.

I've made an example to demonstrate this. Please unzip the attachment and copy all FlowHeater EXE and DLL files into the same unzip folder. NOTE: If you do not copy the DLL and EXE files to this folder you have to change the paths in the batch-execute.cmd file.

Collecting all Information into one Excel file)

1. Open the collecting.fhd definition
2. Open the configurator for the READ side and select the excel file info-1.xls
3. Execute the definition
4. Repeat the steps 2 to 3 with all excel files (info-2.xls, info-3.xls, ...)

After all, the result is you have one Excel file "collecting.xls" with the consolidated information.


Sort and remove duplicate content)
The remove-duplicate.fhd definition firstly sorts the data from the Excel workbook "collecting.xls" by the fields "NR" and "Description". After sorting FlowHeater remove the duplicate content with the GroupBy Heater by using the same fields "NR" and "Description".


Automatically executing)
These steps above you can run automatically by executing the batch-execute.cmd script. Please have a look inside. For a description for the forefiles command, please have look to the example: Import several CSV text files automatically .

The same way you can collect other information on other places in the Excel worksheet; you just have to create one more FlowHeater definition.

Hope it's a bit clearer.