Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
I am having some revision numbers say c1, c2,c3,c4.... I want to know how to
make a search in column that contains C1 to C100 randomly (may be some missing) and return true if that is present and false if it is not. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
One equivalent way
Assume the base list is in col A Assume your own list is in B2 down Put in C2: =IF(B2="","",IF(COUNTIF(A:A,B2)0,"Present","Not found")) Copy C2 down to the last row of data in col B Col C will return: Present for items in col B found in col A Not found for items in col B not found in col A You could then easily apply autofilter on col C to filter out as desired -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: I am having some revision numbers say c1, c2,c3,c4.... I want to know how to make a search in column that contains C1 to C100 randomly (may be some missing) and return true if that is present and false if it is not. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
Thank u Max,
I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" "Max" wrote: One equivalent way Assume the base list is in col A Assume your own list is in B2 down Put in C2: =IF(B2="","",IF(COUNTIF(A:A,B2)0,"Present","Not found")) Copy C2 down to the last row of data in col B Col C will return: Present for items in col B found in col A Not found for items in col B not found in col A You could then easily apply autofilter on col C to filter out as desired -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: I am having some revision numbers say c1, c2,c3,c4.... I want to know how to make a search in column that contains C1 to C100 randomly (may be some missing) and return true if that is present and false if it is not. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
Hi,
In cell S10, use the following formula and then copy down: =if(countif(B10:B20,C3)0,"True","False") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "2226" wrote in message ... Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" "Max" wrote: One equivalent way Assume the base list is in col A Assume your own list is in B2 down Put in C2: =IF(B2="","",IF(COUNTIF(A:A,B2)0,"Present","Not found")) Copy C2 down to the last row of data in col B Col C will return: Present for items in col B found in col A Not found for items in col B not found in col A You could then easily apply autofilter on col C to filter out as desired -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: I am having some revision numbers say c1, c2,c3,c4.... I want to know how to make a search in column that contains C1 to C100 randomly (may be some missing) and return true if that is present and false if it is not. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
Believe there was nothing wrong with the logic in the response earlier
Anyway, since you have revised your description Put in S10: =IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE)) If the above settles it for you (it should), pl press the YES button below to acknowledge -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
Thank u for ur time, still Im not able to find correct logic. See I have a
column in which there are alphanumerical in cell B10 to B20 (red 3, red4, red6, red9, red10, red 15, red17, red18, red20, red23). I have cell in which the input has given say cell A10 (red5) & I have a cell which will tell whether input in A10 is present from the B10 to B20 or not say cell S10. If Im putting the logic statement in cell S10 the logic is results in €śfalse€ť, when I change the input in A10 as red4, which is present in cell B10 to b20 then also the answer is €śfalse€ť but it has to be €śtrue" "Max" wrote: Believe there was nothing wrong with the logic in the response earlier Anyway, since you have revised your description Put in S10: =IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE)) If the above settles it for you (it should), pl press the YES button below to acknowledge -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
You need to be more consistent with your data. In your examples you
quote red 3 and red 15 (i.e. with spaces in them), but also red4, red5 etc (without spaces). I suspect this is what is causing you problems. Hope this helps. Pete On Jan 5, 9:39*am, 2226 wrote: Thank u for ur time, still I’m not able to find correct logic. See I have a column in which there are alphanumerical in cell B10 to B20 (red 3, red4, red6, red9, red10, red 15, red17, red18, red20, red23). I have cell in which the input has given say cell A10 (red5) & I have a cell which will tell whether input in A10 is present from the B10 to B20 or not say cell S10. If I’m putting the logic statement in cell S10 the logic is results in “false”, when I change the input in A10 as red4, which is present in cell B10 to b20 then also the answer is “false” but it has to be “true" "Max" wrote: Believe there was nothing wrong with the logic in the response earlier Anyway, since you have revised your description Put in S10: =IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE)) If the above settles it for you (it should), pl press the YES button below to acknowledge -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. *B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10"- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
I have test Max's formula and its works.
The database need to consistency. You can't have a range of cells with "red 3", "red10"..etc where there are space in some cells and not in others. Try changing all your data table to "red 3", "red 10" by adding a space in between the word and number, then place the formula in the cells you want. Bear in mind that the value in A10 should also be the same as your data in col B. HTH -- If this posting was helpful, please click on the Yes button below Thank You cheers, francis "2226" wrote: Thank u for ur time, still Im not able to find correct logic. See I have a column in which there are alphanumerical in cell B10 to B20 (red 3, red4, red6, red9, red10, red 15, red17, red18, red20, red23). I have cell in which the input has given say cell A10 (red5) & I have a cell which will tell whether input in A10 is present from the B10 to B20 or not say cell S10. If Im putting the logic statement in cell S10 the logic is results in €śfalse€ť, when I change the input in A10 as red4, which is present in cell B10 to b20 then also the answer is €śfalse€ť but it has to be €śtrue" "Max" wrote: Believe there was nothing wrong with the logic in the response earlier Anyway, since you have revised your description Put in S10: =IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE)) If the above settles it for you (it should), pl press the YES button below to acknowledge -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
I apologize Max, ur logic function works perfectly. I have used it on a wrong
sheet where I had made so many changes to get the results, it actually works. I had a problem with the work sheet. When I tried it on the other sheet it was working. Thank u very much making my life easy. Thank u Max. "Max" wrote: Believe there was nothing wrong with the logic in the response earlier Anyway, since you have revised your description Put in S10: =IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE)) If the above settles it for you (it should), pl press the YES button below to acknowledge -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote: Thank u Max, I have tried ur logic but it is not working, all results are "not found". Let me explain once again my prob, in one cell I have an input say "C3" (say cell no. A10), I need to check whethere that is present in a column(say cell no. B10 to B20), if it is present then it should return true or else it should return false. For this I need to write a function in the cell say "cell no. S10" |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search an alphanumeric in a column and return true/false
Ah, no prob. Just glad you got it going over there.
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "2226" wrote in message ... I apologize Max, ur logic function works perfectly. I have used it on a wrong sheet where I had made so many changes to get the results, it actually works. I had a problem with the work sheet. When I tried it on the other sheet it was working. Thank u very much making my life easy. Thank u Max. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
Search column for value and return TRUE or FALSE | Excel Worksheet Functions | |||
Look up to return a true/false value | Excel Worksheet Functions | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
How do I return True False | Excel Worksheet Functions |