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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bevpike - 13 Sep 2007 21:56 GMT

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

Reply to this Message

Pete_UK - 13 Sep 2007 22:56 GMT

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

> I would like to merge data from two worksheets into one worksheet based on a
> key data value. Here is the data:
[quoted text clipped - 35 lines]
>
> Attila

Reply to this Message