Hi David,

Nothing attached. Please add your file again and then click the orange 'start upload' button to upload the file.

In the meantime from what you describe I'd be using Power Query to merget the tables, or Power Pivot to create a relationship between the tables just like you have in Access, as opposed to VBA.

When we see your data we can give you an example.

Mynda

Sorry forgot attach

Thanks, David.

You can use Power Query to easily merge ELE_Sheet-A and TAK_Sheet-A files into something that resembles the 'Main.xlsx' file, since these have the same structure (i.e. column headings are identical).

However, I'm not clear on what you want to do with file 'Sheet-B.xlsx' as it has different column headings. What role does this file play?

Mynda

Thanks Mynda.

Can you show me the steps  how to use the power query  to merge ELE_Sheet-A and TAK_Sheet-A files into Main.xlsx base on the mapping criteria specified in workshhet Field-Map Condition.xlsx, appreciate that . 

Hi David,

Follow these steps:

1. Put your two files into a folder on their own. i.e. no other files in that folder.

2. change the sheet names in each file so they're identical

3. In a new workbook: Power Query tab > From File > From Folder

4. Browse to the folder containing the two Excel files you want to consolidate > click OK

5. Click Edit at the next dialog box

6. Select the first two columns > right-click > remove other columns

7. Add column tab > Custom Column > enter this formula in the formula field (exactly as I've written it here) and then click ok:

=Excel.Workbook( [Content] )

8. Select the Content field and press DELETE

9. Click the double headed arrow in the Custom column header.

10. Deselect 'Use original column name as prefix' > click OK

11. Click the filter on the 'Name.1' column and deselect '_Xlmn._FilterDatabase'

12. Select Name and Data columns (hold CTRL to select non-contiguous columns) > right-click > remove other columns

13. Click the double headed arrow on the Data column > Make usre 'use original column name as prefix' is deselected > click ok

14. Transform tab > Use first row as headers

15. Click on the filter button on the 'Change' column > deselect 'Change' > click ok.

16. Select the first column > Transform tab > Split Column: By delimiter > Select 'Custom' delimiter from the list > enter a hyphen as the delimiter > select 'At the right most occurence' > Click OK

17. Select the second column > Transform tab > Split Column: By delimiter > Select 'Custom' delimiter from the list > enter a period/full stop as the delimiter > Click OK

18. Select the first and third columns > press DELETE

19. Double click the header of the first column > type in a new name 'Sheet'

20. In the right-hand pane under 'Query settings' under the 'Properties'  heading give your query a new name in the Name field. This will be the name of yur Excel table containing your merged data.

21. Home tab > Close & Load

You should now have a table containing the contents of both files just like the example attached. If you get stuck you can see the steps in the query in the file attached.

Normally merging files is much more straight forward than this, but there are a lot more steps in this example because you need to retain the workbook name from each file so you can populate the 'Sheet' column. That said, in this example it's all point and click, no complex coding language, just a nice GUI to guide you through.

If you'd like to learn Power Query please consider my course here: https://www.myonlinetraininghub.com/excel-power-query-course

Mynda

Thanks millions Mynda!

Will try on that based on above steps suggested,  also will consider to join the Course soon to learn the power query skills in depth.

Mynda,

Is there  a way to merge Excel Tables [ Fields column position different ] but using Power query to pick these desired fields from the Tables  and match them to a master layout say for example follow the Main.xlsx in the attachment?