asked on
Excel: Find the first instance of a value in column C while searching on column B
I have a data set with Identifiers in column A, a second column containing identifiers in column B and a value in column C.
I want to find all records in column B that has the same identifier as column A, and THEN check the value in column C for each record until I find a value of x. Therefore, I want the formula to stop looking when it finds the value I'm looking for and return TRUE (or false) in column D.
In this example, I want to find the value = 0 in column C. Hence, the formula for row 1 should return TRUE since I found a value of 0 on row 3 where A was found in column B.
A B C D
1 N4 N5 1 TRUE
2 N8 N4 1 FALSE (Because it can't find A in column B)
3 N6 N4 0 FALSE (Because it can't find A in column B)
4 N7 N4 1 FALSE (Because it can't find A in column B)
Any thoughts ?
Thanks
I want to find all records in column B that has the same identifier as column A, and THEN check the value in column C for each record until I find a value of x. Therefore, I want the formula to stop looking when it finds the value I'm looking for and return TRUE (or false) in column D.
In this example, I want to find the value = 0 in column C. Hence, the formula for row 1 should return TRUE since I found a value of 0 on row 3 where A was found in column B.
A B C D
1 N4 N5 1 TRUE
2 N8 N4 1 FALSE (Because it can't find A in column B)
3 N6 N4 0 FALSE (Because it can't find A in column B)
4 N7 N4 1 FALSE (Because it can't find A in column B)
Any thoughts ?
Thanks
Microsoft OfficeMicrosoft ExcelMicrosoft Applications
Last Comment
herbalizer
ASKER
Thanks. However, this only tells me if I can find A in column B. What I want to do is then check a different column (column C) and verify the value until I get the one I'm looking for. I've refined the example below
A B C D
1 N4 N5 1 TRUE
2 N8 N4 1 FALSE (Because although it did find N8 in column B (row 5), C=1 in row 5 (and I want to find a 0)
3 N6 N4 0 FALSE (Because it can't find A in column B)
4 N7 N4 1 FALSE (Because it can't find A in column B)
5 N9 N8 1 FALSE (Because it can't find A in column B)
Thanks
A B C D
1 N4 N5 1 TRUE
2 N8 N4 1 FALSE (Because although it did find N8 in column B (row 5), C=1 in row 5 (and I want to find a 0)
3 N6 N4 0 FALSE (Because it can't find A in column B)
4 N7 N4 1 FALSE (Because it can't find A in column B)
5 N9 N8 1 FALSE (Because it can't find A in column B)
Thanks
Then give this a try...
In D1
In D1
=IF(COUNTIFS(B:B,A1,C:C,"0"),TRUE,FALSE)
ASKER
Yes. that'll do it !! Thanks
Not very familiar with excel ...
Not very familiar with excel ...
ASKER
Hi,
I hate to throw a wrench in there but while it works, it takes too much processing time to apply this formula to a list of 10000 units, which is the size of the file.
Does anybody have a solution that is more efficient in terms of systems resource being used ?
Thanks
I hate to throw a wrench in there but while it works, it takes too much processing time to apply this formula to a list of 10000 units, which is the size of the file.
Does anybody have a solution that is more efficient in terms of systems resource being used ?
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes, I had done that in the meantime, but the file was also bigger than it needed to be so I cleaned it up and it works fine now.
Thanks a lot !
Thanks a lot !
You're welcome! Glad it is working as desired.
Please close the question by accepting the answer.
Please close the question by accepting the answer.
ASKER
thanks
In D1
Open in new window
and copy it down.