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

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

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

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

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

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

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

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.

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.

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
---

Finding unique entries among two columns of alphanumeric data
 

"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

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