Finding unique entries among two columns of alphanumeric data

[Guest]

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

 

[Guest]

One play using non-array formulas ..

Assume data in cols A & B, from row1 down

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

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

Select C11, 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

 

[Guest]

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

 

[Guest]

Glad it worked fine, Bob !
Thanks for the feedback ..

 

[Guest]

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

 

[Guest]

Try these slight adjustments ..

In C2:
=IF(COUNT(D)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(D,ROW(A1)),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 C22, copy down to last row of data in col B, as before

 

[Guest]

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

 

[Guest]

You're welcome, Bob !

 

[Guest]

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

 

[Guest]

.. 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)<ROW(A1),"",INDEX(A:A,MATCH(SMALL(D,ROW(A1)),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 C22, 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

---

 

[Guest]

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

Can this be used to count blank entries in each row among several columns
(over 12 columns)?
LLS