Merge data in two worksheets based on key data value (like databas
In Sheet3 enter these formulae in A1 and B1:
A1: =IF(Sheet1!A1="","",Sheet1!A1)
B1: =IF(Sheet1!B1="","",Sheet1!B1)
then copy these down the columns for at least as many entries as you
have in Sheet1 - you will get blanks if there is no data in the
corresponding cells in Sheet1.
Then in C1 enter this formula:
=IF($B1="","",VLOOKUP($B1,Sheet2!$A1:$D3,COLUMN(B1 ),0))
and copy the formula into D1:E1. Note that I have assumed that you
only have 3 rows in Sheet2, so adjust the range to suit your data.
Then copy C1:E1 down the columns until you run out of data in columns
A and B.
If you want to fix the values, then highlight the range in Sheet3,
click <copy followed by Edit | Paste Special | Values (check) | OK
then <Enter.
Hope this helps.
Pete
On Sep 13, 9:56 pm, bevpike wrote:
I would like to merge data from two worksheets into one worksheet based on a
key data value. Here is the data:
Worksheet 1
ColA ColB
DAVE 001
SAM 001
JIM 001
MARY 002
WENDY 002
JUANA 003
Worksheet 2
ColA ColB ColC ColD
001 life 1 01-Jan
002 health 2 01-Mar
003 disability 3 01-Feb
In a third worksheet I want to merge the data to get the following:
ColA ColB ColC ColD ColE
DAVE 001 life 1 01-Jan
SAM 001 life 1 01-Jan
JIM 001 life 1 01-Jan
MARY 002 health 2 01-Mar
WENDY 002 health 2 01-Mar
JUANA 003 disability 3 01-Feb
You will notice that the data is merged based on the link between Col B from
Worksheet 1 and ColA in worksheet 1. I know this is easily done in a
database but I need to do it in Excel.
Is this possible.
Thanks in advance for your help.
Attila
|