# How to join two columns/lists of data?

1. ## How to join two columns/lists of data?

I have a list of hundreds cases. I need to match this list against a much larger list of thousands of cases. The output should be to align the rows of the two lists, or otherwise highlight the matches. There must be a super simple way to do this, but I'm not having any luck getting match or index or lookup formulas to work.

Thanks!

2. ## Re: How to join two columns/lists of data?

There is surely a way to do it but without seeing your workbook there's no way to say for sure....
Please post a sample workbook if possible.

3. ## Re: How to join two columns/lists of data?

Here are the first few rows. Note that the first match is in the last row of column A (value = 206013). I need the data in the columns A and B to line up, so that the value in column C is correctly aligned with the case number in column A.

Another way to do this would be to add rows above column B and C until they line up with column A. I have a macro that does this, but it's just ridiculously slow

206001 206013 B
206002 206020 B
206003 206027 B
206004 206047 B
206005 206052 B
206006 206057 B
206007 206073 B
206008 206082 DC
206009 206088 B
206010 206098 B
206011 206099 C
206012 206108 A
206013 206124 B

Thanks!

4. ## Re: How to join two columns/lists of data?

Does column A contains all possible values?
If not, where would you put the values from Column B and C if they have no match in column A?

5. ## Re: How to join two columns/lists of data?

You did not really say which column you want to compare against which, so this 1 compares each cell in column A, against all cells in column B, and where there is a match, pi returns the value in C.

If this is not what you want, 1st, try swapping the references/ranges around, if that still is not what you want, please provide a sample of your expected output

 A B C D 1 206001 206013 B 2 206002 206020 B 3 206003 206027 B 4 206004 206047 B 5 206005 206052 B 6 206006 206057 B 7 206007 206073 B 8 206008 206082 DC 9 206009 206088 B 10 206010 206098 B 11 206011 206099 C 12 206012 206108 A 13 206013 206124 B B

D1=IFERROR(INDEX(\$C\$1:\$C\$13,MATCH(A1,\$B\$1:\$B\$13,0)),"")
copied down

6. ## Re: How to join two columns/lists of data?

Yes, column A contains all possible values.

7. ## Re: How to join two columns/lists of data?

Thank you, this works perfectly!

8. ## Re: How to join two columns/lists of data?

Here's a VBA solution that you can try.
It will align B,C values with A and put at the end non matching values...
Result output in E, F, G.

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1