Question
190 views

Combining data from two Excel worksheets, with one common field.

c19587 asked on

I have two columns of text and data in tab-1 of my Excel 2007 workbook.  Column “A” contains a list of unique alphanumeric names and column “B” contains a list of the alphanumeric IDs - one unique ID for each unique name.   The list will be several hundred rows long.

TAB-1

COLUMN A

COLUMN B

PEN RED

1234

COMPUTER BLUE

5678

 

In tab-2 of the same workbook, column “A” is empty and I have a list of unique IDs in column B, some of which are the same as the IDs on tab-1.  To the right of the IDs on tab-2, I have several columns of information for each unique ID. 

TAB-2

COLUMN A

COLUMN B

COLUMN C

COLUMN D

 

1234

RED

Pink

 

5678

BLUE

Purple

 

2121

BLACK

GRAY

 

3434

GREEN

LIME

 

How can I copy the data from column A tab-1, to the appropriate rows in column A tab-2?

In other words, how can I copy the hundreds of unique names from tab-1 to tab 2 and have each name be on the same rows as its partner ID #? 

I've never done anything like this before in Excel.  I'm confident it can be done, but I'm stumped as to how.

2 people had this question

Abuse history


The answered status icon Answer
Wox33 replied on
Assuming you have headers and data starts in row 2. Try this in A2 of tab-2 and fill down. Replace 99 with the actual number of rows you have.

=INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0)))

or this to replace the errors where there is no match:

=IF(ISERROR(INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0)))),"",INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0))))

Replace tab-1 and tab-2 with Sheet1 and Sheet2 if you actually did not rename these.


Be the first person to mark this helpful

Abuse history


progress