CODEWITHSUNDEEP
arraysexcelmergearray-merge
BrianC
BrianC

Reputation: 29

I want to merge two excel workbooks by specific column data?

The problem I have is I need to merge two excel workbooks by common column data (Column A in both workbooks are the same data - names) but workbook 2 has more individual named data than workbook 1, in column A, so I just want the data from workbook 2 that matches the data from workbook 1 - and to skip over (leave out) the excess data in workbook 2? - if possible I'd also like to only extract certain columns from workbook 2 to merge with workbook 1 also. Is this possible? The files are too big to manually remove unnecessary rows by filter. Thanks

Upvotes: 0

Views: 137

Answers (2)

BrianC
BrianC

Reputation: 29

thanks both. Only getting back to this as have been away. Jahknows, in response to your questions, the only column that actually matches is the first column (column A). And yes, I want to append the data from workbook 2 to workbook 1 - based on the names in column A matching in both workbooks - workbook 2 will have more names in column A than workbook 1. this is excess data that is not needed in the appended workbook.

Thanks TDP - I've had a quick play around with the power query and this could be the answer when I've fully figured out how to append what I want and have leave out the excess data I referred to.

Upvotes: 0

TDP
TDP

Reputation: 1231

You could use Power Query (preferably in a third workbook.)

Have one query for each workbook/column. The two queries can be combined into one query so all the data is together.

Preferably define the queries as Connection Only and load the resulting data directly up into a Power Pivot data model.

You have all sorts of ETL (extract, transform, load) capabilities in Power Query.

Access the data via a pivot table (in the third workbook.)

If your datasets are massive I'd upgrade to 64bit Office first.

Upvotes: 0

Related Questions