| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indentify value from multiple values in a single cell
I am looking to produce a formula that shows if a number is "found" or "not
found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
Can you post a small example?
If you data looks like this: Column A 45 1 84 99 8 Column H 1,12,20 32,44,55 18,99,65 In column B enter this formula and copy down: =IF(SUMPRODUCT(--(ISNUMBER(FIND(","&A1&",",","&H$1:H$3&",")))),"mat ch","") Column B will return "match" for 1 and 99 Biff "Dave" wrote in message ... I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
=countif(sheet2!a:a,"*"&a1&"*")0
Will return True if it's found. You may want to check for empty cells: =if(a1="","",countif(sheet2!a:a,"*"&a1&"*")0) or =isnumber(match("*"&a1&"*",sheet2!a:a,0)) or =if(a1="","",isnumber(match("*"&a1&"*",sheet2!a:a, 0))) Dave wrote: I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
Suppose the number in A1 is 1.
Sheet2!An = 11,21,31 =countif(sheet2!a:a,"*"&a1&"*")0 returns TRUE Biff "Dave Peterson" wrote in message ... =countif(sheet2!a:a,"*"&a1&"*")0 Will return True if it's found. You may want to check for empty cells: =if(a1="","",countif(sheet2!a:a,"*"&a1&"*")0) or =isnumber(match("*"&a1&"*",sheet2!a:a,0)) or =if(a1="","",isnumber(match("*"&a1&"*",sheet2!a:a, 0))) Dave wrote: I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
Yes it does.
Biff wrote: Suppose the number in A1 is 1. Sheet2!An = 11,21,31 =countif(sheet2!a:a,"*"&a1&"*")0 returns TRUE Biff "Dave Peterson" wrote in message ... =countif(sheet2!a:a,"*"&a1&"*")0 Will return True if it's found. You may want to check for empty cells: =if(a1="","",countif(sheet2!a:a,"*"&a1&"*")0) or =isnumber(match("*"&a1&"*",sheet2!a:a,0)) or =if(a1="","",isnumber(match("*"&a1&"*",sheet2!a:a, 0))) Dave wrote: I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
Dave, Bif,
Thanks for the replies. I am a lot closer than I was. Is there an addition to the formula that can give me an exact match? The example that Bif wrote below is exactly whats happening with some of my data. -- dmp "Dave Peterson" wrote: Yes it does. Biff wrote: Suppose the number in A1 is 1. Sheet2!An = 11,21,31 =countif(sheet2!a:a,"*"&a1&"*")0 returns TRUE Biff "Dave Peterson" wrote in message ... =countif(sheet2!a:a,"*"&a1&"*")0 Will return True if it's found. You may want to check for empty cells: =if(a1="","",countif(sheet2!a:a,"*"&a1&"*")0) or =isnumber(match("*"&a1&"*",sheet2!a:a,0)) or =if(a1="","",isnumber(match("*"&a1&"*",sheet2!a:a, 0))) Dave wrote: I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indentify value from multiple values in a single cell
Did you try the formula I suggested?
It will only "work" for exact matches. Biff "Dave" wrote in message ... Dave, Bif, Thanks for the replies. I am a lot closer than I was. Is there an addition to the formula that can give me an exact match? The example that Bif wrote below is exactly whats happening with some of my data. -- dmp "Dave Peterson" wrote: Yes it does. Biff wrote: Suppose the number in A1 is 1. Sheet2!An = 11,21,31 =countif(sheet2!a:a,"*"&a1&"*")0 returns TRUE Biff "Dave Peterson" wrote in message ... =countif(sheet2!a:a,"*"&a1&"*")0 Will return True if it's found. You may want to check for empty cells: =if(a1="","",countif(sheet2!a:a,"*"&a1&"*")0) or =isnumber(match("*"&a1&"*",sheet2!a:a,0)) or =if(a1="","",isnumber(match("*"&a1&"*",sheet2!a:a, 0))) Dave wrote: I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump. Any help is appreciated. -- dmp -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto fill multiple cells depending on single cell value | Excel Worksheet Functions | |||
| How do I Auto-Filter with multiple values in a cell in Excel? | Excel Discussion (Misc queries) | |||
| How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
| city, state, zip from a single cell to multiple cells | Excel Discussion (Misc queries) | |||
| multiple validation in a single cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
