Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare sheets for matches
Hello,
I have a problem as I'm attempting to use a formula previously provided by Bob Phillips on 10/23/07. This is the formula: =IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found") I tried using this formula but it would not work correctly. I believe my problem has to do with my column A data has a combination of numbers and text, for example 45RAC60098V23. My column B data has only one text letter such as R in which case it also uses the color red as the cell's fill color. It may be either R(red), G (green), Y(yellow) as well. I believe the formula would work if I only had numbers in my two columns. I appreciate any assistance in resolving this issue. -- By persisting in your path, though you forfeit the little, you gain the great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare sheets for matches
The text/number property won't make any difference.
Your formula looks a little weird: =IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)), IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found") Did you really mean to look at 'sheet a' for B2? Maybe it's a simple typo. My next guess is that your data isn't what you expect. Debra Dalgleish has some more notes on trouble shooting these kinds of formulas: http://www.contextures.com/xlFunctions02.html#Trouble ===== ps. Your formula isn't looking for a match on the same row--it's just looking for a match in those columns. Is that what you really wanted? DavidH56 wrote: Hello, I have a problem as I'm attempting to use a formula previously provided by Bob Phillips on 10/23/07. This is the formula: =IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found") I tried using this formula but it would not work correctly. I believe my problem has to do with my column A data has a combination of numbers and text, for example 45RAC60098V23. My column B data has only one text letter such as R in which case it also uses the color red as the cell's fill color. It may be either R(red), G (green), Y(yellow) as well. I believe the formula would work if I only had numbers in my two columns. I appreciate any assistance in resolving this issue. -- By persisting in your path, though you forfeit the little, you gain the great. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare sheets for matches
Thank you Dave for your quick response. I got it to work okay. I looked
closely at what I had. I had initially used columns F and B. When I readjusted to use A and B, it worked beautifully. Thanks again for your response. -- By persisting in your path, though you forfeit the little, you gain the great. "Dave Peterson" wrote: The text/number property won't make any difference. Your formula looks a little weird: =IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)), IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found") Did you really mean to look at 'sheet a' for B2? Maybe it's a simple typo. My next guess is that your data isn't what you expect. Debra Dalgleish has some more notes on trouble shooting these kinds of formulas: http://www.contextures.com/xlFunctions02.html#Trouble ===== ps. Your formula isn't looking for a match on the same row--it's just looking for a match in those columns. Is that what you really wanted? DavidH56 wrote: Hello, I have a problem as I'm attempting to use a formula previously provided by Bob Phillips on 10/23/07. This is the formula: =IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found") I tried using this formula but it would not work correctly. I believe my problem has to do with my column A data has a combination of numbers and text, for example 45RAC60098V23. My column B data has only one text letter such as R in which case it also uses the color red as the cell's fill color. It may be either R(red), G (green), Y(yellow) as well. I believe the formula would work if I only had numbers in my two columns. I appreciate any assistance in resolving this issue. -- By persisting in your path, though you forfeit the little, you gain the great. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare all matches | Excel Programming | |||
Compare two lists for matches | Excel Programming | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
Compare columns, count matches | Excel Worksheet Functions | |||
I need to compare to columns and indicate the matches in another | New Users to Excel |