merging data from 2 sheets based on a join value
Posted on 2016-09-22
I have an xlsx with 2 sheets, in the first sheet, customer data is in a row by row format, i.e
Custreference forename surname address 1 address 2 etc
12345 EE user 123 street london
At the end of sheet 1 are 3 blank columns (phone, mobile, email), and I need to populate them with data from sheet 2
In sheet 2 are 3 columns
Custreference type value
12345 phone 999888
12345 mobile 777666
12345 email firstname.lastname@example.org
The only way to join the records is via the customer reference field which is on both sheets. What i need really is 3 formulas in the 3 columns in sheet 1, to check all values of sheet 2, and for example in the phone column, compare all the rows in sheet 2, where type = phone, and reference matches from both sheets, to populate the phone column with the value for that reference in sheet 2.
Likewise for the mobile column, check all rows in sheet 2 where reference matches, and type = mobile, populate the mobile column in sheet 1 with the mobile value in sheet 2 for that reference.