How do I merge multiple CSV files with a common column

Question

How do I merge multiple CSV files with a common column

I have several csv files that I want to merge into one master file and use in Numbers version 3.6.1(2566) on OS X 10.11.4. The files have a common column that identifies individual customers. I need to merge (not concatenate) the data into one master file based on the common column called 'ContactID.' One file has the customer first and last name, another file has their email address, etc., in addition to the ContactID.

Any ideas would be greatly appreciated. Thanks in advance.

Bill

OS X El Capitan (10.11.4)

Posted on Apr 28, 2016 6:46 AM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Apr 28, 2016 8:17 AM in response to William Moore1

I would open each CSV file and copy that data into a table in a new document.

Name.csv --> Table named Name

Email.csv --> Table named Email

.

xxxx.csv --> Table named xxxx

Here is an example:

I am "connecting" things using a name. This means that typing the table names and column names is very important.

Assuming you can get that data from the CSV file to a table like shown above you should do the following:

1) make sure the first row of each table is a header row

2) make sure the table name is consistent between the table and the name use in the summary table

3) make sure the summary table has two header rows

for the summary table:

make sure the contact ID is unique (if you need help with this post back)

B3=VLOOKUP($A3, INDIRECT(B$1&"::A:"&CHAR(CODE("A") + COLUMNS(INDIRECT(B$1&"::1:1",addr-style),headers)−1)), MATCH(B$2,INDIRECT(B$1&"::1:1",addr-style)), 0)

this is shorthand for... select cell B3 then type (or copy and paste from here) the formula:

=VLOOKUP($A3, INDIRECT(B$1&"::A:"&CHAR(CODE("A") + COLUMNS(INDIRECT(B$1&"::1:1",addr-style),headers)−1)), MATCH(B$2,INDIRECT(B$1&"::1:1",addr-style)), 0)

select cell B3, copy

select cells B3 thru the end of column D, paste

Reply