Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using functions to compare multiple columns for mismatch of cells
Hmmm... having only 10 words as a subject to describe this problem seems
rather limiting. Come on, Microsoft, let me have 15 words! I want to use Excel functions to compare multiple columns for mismatch of cell contents. I spoent a lot of time using Excel Help for functions like VLOOKUP and IF, with no luck. I assume there is a functional solution, but I could not figure it out in an hour. These are not easy functions for me to use. The TASK: compare name (text) AND a number in a set of columns with similar (but not exact) info in another set of columns. I want to be able to have a text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number mismatch". There are more entries in one set of columns than another. After much research, and trial & error, I was unsuccessful using a single function to get the desired results. I tried consolidating the text & number information in an additional column using this formula: =B403&" "&F403 - info was in columns B & F Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the consolidated info. The result was showing as, "#N/A", but it did not distinguish between a mismatch of cell contents, or if matching information was missing from a column. Can you help me with this? Mark D. Boston, MA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using functions to compare multiple columns for mismatch of cells
#N/A in this situation would mean that no match could be found in
column G of the lookup table. You could trap this using a construction like =IF(ISNA(lookup_formula),"no match",lookup_formula). Your vlookup formula has a third parameter of 1, meaning to return data from the first column of the table - presumably you intended this to be 2? If it was set to 2 and there was a match on column 1 of the table but no data in the adjacent column, you would get a returned value of 0, even if the cell was empty. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using functions to compare multiple columns for mismatch of cells
For vlookup, Lookup_value (the first cell you put in the formula) is the
value to be found in the first column of the array-meaning you have to use column A for the value to lookup, otherwise vlookup won't work. You can only compare one item at a time, but you can just copy the vlookup formula over to the next column. "MDIAZ451" wrote: Hmmm... having only 10 words as a subject to describe this problem seems rather limiting. Come on, Microsoft, let me have 15 words! I want to use Excel functions to compare multiple columns for mismatch of cell contents. I spoent a lot of time using Excel Help for functions like VLOOKUP and IF, with no luck. I assume there is a functional solution, but I could not figure it out in an hour. These are not easy functions for me to use. The TASK: compare name (text) AND a number in a set of columns with similar (but not exact) info in another set of columns. I want to be able to have a text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number mismatch". There are more entries in one set of columns than another. After much research, and trial & error, I was unsuccessful using a single function to get the desired results. I tried consolidating the text & number information in an additional column using this formula: =B403&" "&F403 - info was in columns B & F Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the consolidated info. The result was showing as, "#N/A", but it did not distinguish between a mismatch of cell contents, or if matching information was missing from a column. Can you help me with this? Mark D. Boston, MA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using functions to compare multiple columns for mismatch of cells
If you can change the columns into text file, then you can try Texteer
software at: http://www.texteer.com It provides several functions in which "Include Compare" function maybe can solve your problem.The quote page:http://www.texteer.com/windows/b0.sb1.1.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use functions when cells are merged | Excel Worksheet Functions | |||
Functions across multiple worksheets | Excel Worksheet Functions | |||
Compare data - one cell to multiple cells | Excel Discussion (Misc queries) | |||
Arithmetical Mode Value for Filtered cells in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions |