Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value of non matches in columns
I am not sure what is the best way to do this: I have two columns that in a
worksheet are usually a few rows off. Column A contains a list of all the account numbers Column B is missing some of the account numbers. I need to find the missing accout numbers and enter them in Column C. In the reference below I would want to know that cell A5 AAC100L is not located in any cell in Column B. I am looking for non matches between the columns. I tries a couple of different formulas but they stop when the columns are not an exact match on the same row. Any suggestions? Column A Column B AAC100A AAC100A AAC100C AAC100C AAC100H AAC100H AAC100L AAC100M AAC100M AAC100N AAC100N AAC100P Thank you, Lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value of non matches in columns
In C1 enter =IF(COUNTIF($B$1:$B$6,A1),"", A1 & " is missing")
Copy it down the column Or use: =IF(ISNA(VLOOKUP(A4,$B$1:$B$6,1,0)),"Missing & A1", "") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Gemi" wrote in message ... I am not sure what is the best way to do this: I have two columns that in a worksheet are usually a few rows off. Column A contains a list of all the account numbers Column B is missing some of the account numbers. I need to find the missing accout numbers and enter them in Column C. In the reference below I would want to know that cell A5 AAC100L is not located in any cell in Column B. I am looking for non matches between the columns. I tries a couple of different formulas but they stop when the columns are not an exact match on the same row. Any suggestions? Column A Column B AAC100A AAC100A AAC100C AAC100C AAC100H AAC100H AAC100L AAC100M AAC100M AAC100N AAC100N AAC100P Thank you, Lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value of non matches in columns
Silly typo in second formula, should be
=IF(ISNA(VLOOKUP(A1,$B$1:$B$6,1,0)),"Missing " & A1, "") -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Gemi" wrote in message ... I am not sure what is the best way to do this: I have two columns that in a worksheet are usually a few rows off. Column A contains a list of all the account numbers Column B is missing some of the account numbers. I need to find the missing accout numbers and enter them in Column C. In the reference below I would want to know that cell A5 AAC100L is not located in any cell in Column B. I am looking for non matches between the columns. I tries a couple of different formulas but they stop when the columns are not an exact match on the same row. Any suggestions? Column A Column B AAC100A AAC100A AAC100C AAC100C AAC100H AAC100H AAC100L AAC100M AAC100M AAC100N AAC100N AAC100P Thank you, Lisa |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value of non matches in columns
Try one of these array formulas** :
Assume your data is in the range A2:B7 The "quick and dirty" method: Array entered** in C2: =INDEX(A$2:A$7,SMALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7 ,0)),ROW(A$2:A$7)),ROWS(C$2:C2))-ROW(A$2)+1) Copy down until you get #NUM! errors meaning all data has been extracted. The "robust" method: Array entered** in C2: =IF(ROWS(C$2:C2)<=SUM(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SM ALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7,0)),ROW(A$2:A$7) ),ROWS(C$2:C2))-MIN(ROW(A2:A7))+1),"") Copy down until you get blanks meaning all data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Gemi" wrote in message ... I am not sure what is the best way to do this: I have two columns that in a worksheet are usually a few rows off. Column A contains a list of all the account numbers Column B is missing some of the account numbers. I need to find the missing accout numbers and enter them in Column C. In the reference below I would want to know that cell A5 AAC100L is not located in any cell in Column B. I am looking for non matches between the columns. I tries a couple of different formulas but they stop when the columns are not an exact match on the same row. Any suggestions? Column A Column B AAC100A AAC100A AAC100C AAC100C AAC100H AAC100H AAC100L AAC100M AAC100M AAC100N AAC100N AAC100P Thank you, Lisa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value of non matches in columns
Ooops!
Typo in the "robust" formula. Should be: =IF(ROWS(C$2:C2)<=SUM(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SM ALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7,0)),ROW(A$2:A$7) ),ROWS(C$2:C2))-MIN(ROW(A$2:A$7))+1),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these array formulas** : Assume your data is in the range A2:B7 The "quick and dirty" method: Array entered** in C2: =INDEX(A$2:A$7,SMALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7 ,0)),ROW(A$2:A$7)),ROWS(C$2:C2))-ROW(A$2)+1) Copy down until you get #NUM! errors meaning all data has been extracted. The "robust" method: Array entered** in C2: =IF(ROWS(C$2:C2)<=SUM(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SM ALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7,0)),ROW(A$2:A$7) ),ROWS(C$2:C2))-MIN(ROW(A2:A7))+1),"") Copy down until you get blanks meaning all data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Gemi" wrote in message ... I am not sure what is the best way to do this: I have two columns that in a worksheet are usually a few rows off. Column A contains a list of all the account numbers Column B is missing some of the account numbers. I need to find the missing accout numbers and enter them in Column C. In the reference below I would want to know that cell A5 AAC100L is not located in any cell in Column B. I am looking for non matches between the columns. I tries a couple of different formulas but they stop when the columns are not an exact match on the same row. Any suggestions? Column A Column B AAC100A AAC100A AAC100C AAC100C AAC100H AAC100H AAC100L AAC100M AAC100M AAC100N AAC100N AAC100P Thank you, Lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count values only if matches and return on another worksheet | Excel Worksheet Functions | |||
Is there any way to use HLOOKUP in an array formula to return multiple matches? | Excel Worksheet Functions | |||
How do I use lookup to return multiple matches and sum them? | Excel Discussion (Misc queries) | |||
Vlookup to return the sum of multiple matches | Excel Discussion (Misc queries) | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) |