Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I compare two Excel columns of numbers and see non-matches
I am creating a template to use when comparing a list of telephone numbers,
received on a monthly invoice, against an inventory list of telephone numbers and want to generate a list of the numbers contained in the monthly invoice that do not appear in the inventory. Each list is a column in Excel on the same worksheet and in number format. I have tried using functions that compare one cell reference (i.e. a cell containing a number from the monthly invoice) against a range of numbers (i.e. the column of cells containing the inventory list). The function I have used successfully only works when the number being searched for is at the top of the range. If the number being searched for is somewhere else in the range it is not recognized. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I compare two Excel columns of numbers and see non-matches
Are you using Vlookup? What are you using for the 4th argument
(Range_Lookup)? It can be True (the default if omitted) or False. If True/Omitted (i.e., approximate match) then your inventory list needs to be sorted ascending. If False (i.e., exact match) then sort order doesn't matter. If you have it set to False (which sounds like what you want) and you still only get matches from the begining of your range, double check that the specified range really corresponds to the length of your inventory list. (If you have it set to True and your list isn't sorted, that's one problem. A True setting won't "see" anything past the point where it encounters a number that is smaller than the previous one. Anyways, I don't think you want approximate matches (the 2nd problem), so you should be specifying False.) HTH, -- George Nicholson Remove 'Junk' from return address. "Tcom" wrote in message ... I am creating a template to use when comparing a list of telephone numbers, received on a monthly invoice, against an inventory list of telephone numbers and want to generate a list of the numbers contained in the monthly invoice that do not appear in the inventory. Each list is a column in Excel on the same worksheet and in number format. I have tried using functions that compare one cell reference (i.e. a cell containing a number from the monthly invoice) against a range of numbers (i.e. the column of cells containing the inventory list). The function I have used successfully only works when the number being searched for is at the top of the range. If the number being searched for is somewhere else in the range it is not recognized. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|