+ Reply to Thread
Results 1 to 10 of 10

Finding unique entries among two columns of alphanumeric data

  1. #1
    Bob
    Guest

    Finding unique entries among two columns of alphanumeric data

    Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
    etc.). Many of the Project Numbers in column B are the same as in column A,
    but column B also has additional (i.e., newer) Project Numbers scattered
    throughout. Ideally, I would like to use a built-in function (versus a
    custom function if possible) that compares all the Project Numbers in both
    columns and then separately lists those that are unique to column B.

    Thanks for the help.

    Bob

  2. #2
    Max
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    One play using non-array formulas ..

    Assume data in cols A & B, from row1 down

    In C1:
    =IF(COUNT(D:D)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

    In D1:
    =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))

    Select C1:D1, copy down to the last row of data in col B

    Col C will extract the items unique to col B*, all neatly bunched at the top
    *items in col B not found in col A
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Bob" wrote:
    > Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
    > etc.). Many of the Project Numbers in column B are the same as in column A,
    > but column B also has additional (i.e., newer) Project Numbers scattered
    > throughout. Ideally, I would like to use a built-in function (versus a
    > custom function if possible) that compares all the Project Numbers in both
    > columns and then separately lists those that are unique to column B.
    >
    > Thanks for the help.
    >
    > Bob


  3. #3
    Bob
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Max,
    Your solution (very slick by the way) did the trick! I'm going to study
    your formulas so I understand the logic. Thanks again.
    Bob

    "Max" wrote:

    > One play using non-array formulas ..
    >
    > Assume data in cols A & B, from row1 down
    >
    > In C1:
    > =IF(COUNT(D:D)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))
    >
    > In D1:
    > =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))
    >
    > Select C1:D1, copy down to the last row of data in col B
    >
    > Col C will extract the items unique to col B*, all neatly bunched at the top
    > *items in col B not found in col A
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Bob" wrote:
    > > Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
    > > etc.). Many of the Project Numbers in column B are the same as in column A,
    > > but column B also has additional (i.e., newer) Project Numbers scattered
    > > throughout. Ideally, I would like to use a built-in function (versus a
    > > custom function if possible) that compares all the Project Numbers in both
    > > columns and then separately lists those that are unique to column B.
    > >
    > > Thanks for the help.
    > >
    > > Bob


  4. #4
    Max
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Glad it worked fine, Bob !
    Thanks for the feedback ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Bob" wrote:
    > Max,
    > Your solution (very slick by the way) did the trick! I'm going to study
    > your formulas so I understand the logic. Thanks again.
    > Bob



  5. #5
    Bob
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Max,
    One more thing. I would like to add column headings in rows 1 & 2.
    However, when I do that, the formulas obviously no longer work. Is there a
    way I can adjust your formulas to allow for column headings? Just curious.
    Thanks again,
    Bob

    "Max" wrote:

    > Glad it worked fine, Bob !
    > Thanks for the feedback ..
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Bob" wrote:
    > > Max,
    > > Your solution (very slick by the way) did the trick! I'm going to study
    > > your formulas so I understand the logic. Thanks again.
    > > Bob

    >


  6. #6
    Max
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Try these slight adjustments ..

    In C2:
    =IF(COUNT(D:D)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))
    (above is the same formula as previous, no change. ROW(A1) is always used in
    the starting cell)

    In D2:
    =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW()))
    (Leave D1 empty)

    Select C2:D2, copy down to last row of data in col B, as before

    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Bob" wrote:
    > Max,
    > One more thing. I would like to add column headings in rows 1 & 2.
    > However, when I do that, the formulas obviously no longer work. Is there a
    > way I can adjust your formulas to allow for column headings? Just curious.
    > Thanks again,
    > Bob



  7. #7
    Bob
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Max,
    It worked! You're a genius. Thanks again for all your help.
    Regards, Bob

    "Max" wrote:

    > Try these slight adjustments ..
    >
    > In C2:
    > =IF(COUNT(D:D)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))
    > (above is the same formula as previous, no change. ROW(A1) is always used in
    > the starting cell)
    >
    > In D2:
    > =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW()))
    > (Leave D1 empty)
    >
    > Select C2:D2, copy down to last row of data in col B, as before
    >
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Bob" wrote:
    > > Max,
    > > One more thing. I would like to add column headings in rows 1 & 2.
    > > However, when I do that, the formulas obviously no longer work. Is there a
    > > way I can adjust your formulas to allow for column headings? Just curious.
    > > Thanks again,
    > > Bob

    >


  8. #8
    Max
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    You're welcome, Bob !
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Bob" wrote:
    > Max,
    > It worked! You're a genius. Thanks again for all your help.



  9. #9
    Bob
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    Max,
    Forgive me for bothering you, but I need to switch the data contained in
    columns A and B. In other words, column A will have both the existing and
    newer Project Numbers, whereas column B will simply have just the existing
    Project Numbers. I'm not sure how to modify your formulas to account for the
    change. Can you help?
    Thanks,
    Bob


    "Max" wrote:

    > You're welcome, Bob !
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Bob" wrote:
    > > Max,
    > > It worked! You're a genius. Thanks again for all your help.

    >


  10. #10
    Max
    Guest

    Re: Finding unique entries among two columns of alphanumeric data

    "Bob" wrote:
    > .. I need to switch the data contained in columns A and B.
    > In other words, column A will have both the existing and
    > newer Project Numbers, whereas column B will simply have just the existing
    > Project Numbers. I'm not sure how to modify your formulas to account for the
    > change.


    For the converse situation ..

    In C2:
    =IF(COUNT(D:D)<ROW(A1),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

    [above same as previous, except that we index col A now instead of col B,
    i.e. .. this part: INDEX(A:A, ..]

    In D2:
    =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW() ))

    [above similar to previous criteria, except for the changes to point to col
    A,
    and to match col A's items with what's in col b]

    Select C2:D2, fill down to last row of data in col A*
    *instead of col B

    Col C will return items in col A not found in col B,
    all neatly bunched at the top
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ 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