exclude in search

[ExcelIsMyFave]

I'm new here, so please take it easy on me
This will likely be an easy one for the gurus in here.

Say I have the following:
Cell A1: male
Cell A2: male
Cell A3: female
Cell A4: male

I want to conduct a search using the value in cell B1, and return the row numbers for which the value in B1 exists.

So my formula in C1 is:
=small(if(isnumber(search($B$1, $A$1:$A$A4)), row($A$1:$A$A4)), row(1:1))

and I make it an array by CTRL+SHIFT+enter

So in B1 I type "male" (without the quotes). From C1 to C4 the values populate as 1,2,3,4

However, I only want the rows where "male" exists, in other words, i want an exact match and I want to exclude those cells that have "female"

I'm wanting:
C1 = 1
C2 = 2
C3 = 4
C4 = blank

Is there a way to do this?


Thanks in advance.

 

[ExcelIsMyFave]

Hmmm...

Ok, I'll answer the question as posted...

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:82px;"><col style="width:37px;"><col style="width:37px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">female health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">female health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">6</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">female health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">7</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">9</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">female health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">male health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">female health</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>


Enter this formula in C1. This will return the count of records for the search value.

=COUNTIF(A:A,B1)

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)>C$1,"",SMALL(IF(A$1:A$10=B$1,ROW(A$1:A$10)),ROWS(D$1:D1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

For the most part, your response makes sense to me. In reality, Cells A1 through A10 in my example are actually something like:
A1: cardiovascular, male health
A2: urinary, female health
A3: neurological, male health, female health
etc.
etc

So I want to get the rows that only have "male health"

In the above example I just described here, I expect to get:
D1: 1
D2: 3

So correct me if I'm wrong, but perhaps the formula you're providing uses the lookup value and tries to find cells whose 'value' equals the lookup value?

I suppose I'm asking something a little different.

I'm asking excel to go through column A and tell me all rows where I find the exact text match "male health", but in my example, I need it to exclude the match for cell A2 because it says "female health" (which is not "male health", which is what I'm after)

I really appreciate your help. Thanks again.

 

[T. Valko]

For the most part, your response makes sense to me. In reality, Cells A1 through A10 in my example are actually something like:
A1: cardiovascular, male health
A2: urinary, female health
A3: neurological, male health, female health
etc.
etc

So I want to get the rows that only have "male health"

In the above example I just described here, I expect to get:
D1: 1
D2: 3

So correct me if I'm wrong, but perhaps the formula you're providing uses the lookup value and tries to find cells whose 'value' equals the lookup value?

I suppose I'm asking something a little different.

I'm asking excel to go through column A and tell me all rows where I find the exact text match "male health", but in my example, I need it to exclude the match for cell A2 because it says "female health" (which is not "male health", which is what I'm after)

I really appreciate your help. Thanks again.

I've been trying to get you to post some real data!

Change the formula in C1 to:

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&","," "&A1:A10&","))))

Change the array formula** in D1 to:

=IF(ROWS(D$1:D1)>C$1,"",SMALL(IF(ISNUMBER(SEARCH
(" "&B$1&","," "&A$1:A$10&",")),ROW(A$1:A$10)),
ROWS(D$1:D1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[ExcelIsMyFave]

Awesome, thanks so much.
Can you explain (or point me to a resource that explains) the syntax for the SEARCH function below? (It's nice that I have something that works now, but it would be really nice to understand WHY it works, so hopefully I don't have to bother you or anyone else again regarding this)

=IF(ROWS(D$1:D1)>C$1,"",SMALL(IF(ISNUMBER(SEARCH
(" "&B$1&","," "&A$1:A$10&",")),ROW(A$1:A$10)),
ROWS(D$1:D1)))

I understand the basics of search(find_text,within_text) however I don't understand the use of all the quotation marks and why you need spaces in certain spots when you type everything in between the brackets for the search function. Again, the syntax is throwing me off here, and I understand the basics of how the search function works)

 

[T. Valko]

Awesome, thanks so much.
Can you explain (or point me to a resource that explains) the syntax for the SEARCH function below? (It's nice that I have something that works now, but it would be really nice to understand WHY it works, so hopefully I don't have to bother you or anyone else again regarding this)




I understand the basics of search(find_text,within_text) however I don't understand the use of all the quotation marks and why you need spaces in certain spots when you type everything in between the brackets for the search function. Again, the syntax is throwing me off here, and I understand the basics of how the search function works)

The syntax is due to the nature of your data.

A1: cardiovascular, male health
A2: urinary, female health
A3: neurological, male health, female health

If we want to look for "male health" all 3 cells contain the substring "male health" but the only cells that we're interested in are A1 and A3. Cell A2 contains the substring "female health" but that also contains "male health" at the same time.

Notice the pattern of how the search term is placed in the cell.

[space]male health
[space]female health

And for at least one instance, a cell that contains both terms separated by a comma.

So, we can take advantage of that pattern by looking for [space]term[comma].

If we search for [space]male health[comma] then that eliminates the possibility of getting a "false positive" from "female health".

In the formula we are concatenating spaces to the beginning of the search term and lookup range and concatenating commas to the end of the search term and lookup range.

So, we end up looking for:

[space]male health,
[space]female health,

 

[ExcelIsMyFave]

Biff, that's a great explanation, and it makes much more sense now!

Thanks again!

 

[T. Valko]

Biff, that's a great explanation, and it makes much more sense now!

Thanks again!

You're welcome. Thanks for the feedback!