Merge data in two worksheets based on key data value (like databas

[Guest]

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

 

[Pete_UK]

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