+ Reply to Thread
Results 1 to 8 of 8

How to join two columns/lists of data?

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

    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. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    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.
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

    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. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,085

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

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

    Yes, column A contains all possible values.

  7. #7
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

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

    Thank you, this works perfectly!

  8. #8
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to join 2 lists based on Case Sensitive ID
    By marchandoj in forum Excel General
    Replies: 3
    Last Post: 01-17-2014, 10:11 AM
  2. SQL - Join two columns into one
    By kenny.fsw in forum Access Tables & Databases
    Replies: 4
    Last Post: 08-02-2012, 04:03 AM
  3. Join various columns from two data sheets
    By Apple Ling in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2009, 10:24 PM
  4. join two columns....
    By visitnag in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2008, 01:25 PM
  5. JOIN operation on Excel lists
    By bibikoff in forum Excel General
    Replies: 4
    Last Post: 05-10-2006, 02:55 PM

Bookmarks

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