Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tagging unique items in a list | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Data in table, may need to convert to columns with OFFSET? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions |