Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge data in two worksheets based on key data value (like databas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two worksheets, combine (merge data) | Excel Discussion (Misc queries) | |||
Is it possible to merge data from two worksheets? | Excel Worksheet Functions | |||
matching data to merge two worksheets | Excel Discussion (Misc queries) | |||
How do I merge similar data across different excel worksheets? | Excel Discussion (Misc queries) | |||
Merge Data From Multiple Worksheets | Excel Worksheet Functions |