+ Reply to Thread
Results 1 to 2 of 2

How do I match identical values in 2 columns and then sort?

  1. #1
    Godswatch
    Guest

    How do I match identical values in 2 columns and then sort?

    Hi,

    I have two columns with lots of values. I would like to identify and match
    identical values if they appear in both columns and the sort or list the
    matching pairs. Values that only appear on one column would not be matched.
    What do I do?
    Thanks

    Carlos

  2. #2
    Dave Peterson
    Guest

    Re: How do I match identical values in 2 columns and then sort?

    Say your columns are on Sheet1 in columns A and B.

    I'd create a new worksheet (sheet2?)
    Back to sheet1.
    select the first column and copy|paste into A1 of sheet3

    Then back to sheet1 and copy the data in the second column to the bottom of that
    new list (avoid the header rows).

    Now you have a big old giant list (with duplicates on sheet3).

    Select sheet2
    select columns A
    Data|filter|advanced filter
    check unique records only
    and choose "Copy to another location"

    Use B1 for that output range.

    Debra Dalgleish has some nice instructions at:
    http://www.contextures.com/xladvfilter01.html#FilterUR

    Delete column A (since we're done with it).

    Tnen in B1, put this: On List #1
    In C1, put this: On List #2

    In b2, put this:
    =isnumber(MATCH(a2,sheet1!a:a,0))

    In C2, put this:
    =isnumber(MATCH(a2,sheet1!b:b,0))

    Now drag those formulas down

    Select columns A:C
    Data|filter|autofilter

    You can filter to show which names are on both (show true for both columns)

    Which ones are on one, but not the other (show true for one column and False for
    the other).

    If you have any that show False for both columns, you've made an error.

    Godswatch wrote:
    >
    > Hi,
    >
    > I have two columns with lots of values. I would like to identify and match
    > identical values if they appear in both columns and the sort or list the
    > matching pairs. Values that only appear on one column would not be matched.
    > What do I do?
    > Thanks
    >
    > Carlos


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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