CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-function
Andy K
Andy K

Reputation: 5044

Merge two sheets and keep the redundant data

I have two Excel sheets. First one is like this:

folk_id name first_name town_name
001     blog joe        alamo
002     folk james      brisbane
003     paco yatan      barcelona

Second one is like this:

folk_id music_instrument  color
001     banjo             blue
001     guitar            green 
001     piano             yellow
002     harmonica         grey
003     harpsichord       red
003     violin            orange
003     bass              fuschia

Results would be like this (either in the same sheet or a different one):

  folk_id name first_name town_name music_instrument  color
  001     blog joe        alamo     banjo             blue
  001     blog joe        alamo     guitar            green
  001     blog joe        alamo     piano             yellow
  002     folk james      brisbane  harmonica         grey
  003     paco yatan      barcelona harpsichord       red
  003     paco yatan      barcelona violin            orange
  003     paco yatan      barcelona bass              fuschia

I've tried with VLOOKUP and INDEX but I'm bit confused on how to do it.

I'm looking for a formula and not VBA.

Upvotes: 1

Views: 131

Answers (2)

pnuts
pnuts

Reputation: 59440

Copy second sheet, insert three columns between folk_id and music_instrument (assuming four columns in the source, might actually be only three) then use a lookup function to populate the added columns. Assuming folk_id is then in A1, in B1:

=VLOOKUP($A1,Sheet1!$A$1:$D$4,COLUMN(),0)

copy across and down to suit.

Upvotes: 3

chancea
chancea

Reputation: 5958

This answer covers how to do this on a third sheet. For simplicity lets call each sheet Sheet1, Sheet2, Sheet3

Sheet1 and Sheet2 contain your current data.

The first step is to make a reference in Sheet3 that copies the entire column of folk_id. Because it seems Sheet2 contains the larger amount of folk_id and Sheet1 contains the unique values of folk_id you need to copy Sheet2. To do this just simply call =Sheet2!A2 starting in A2 of Sheet3, and just auto fill down.

Now for each column in where data lies in Sheet1 we will perform the index and match. While for data that is in sheet2 you only need to have reference calls.

Sheet1 Data Referenced using Index and Match:

  1. Name column: =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)) starting in cell B2 and autofill down.
  2. first_name column =INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)) starting in cell C2 and autofill down.
  3. town_name column =INDEX(Sheet1!D:D,MATCH(A2,Sheet1!A:A,0)) starting in cell D2 and autofill down.

Sheet2 Data Referenced normally:

  1. music_instrument column: =Sheet2!B2 starting in cell E2 and autofill down.
  2. color column: =Sheet2!C2 starting in cell F2 and autofill down.

Upvotes: 0

Related Questions