SEARCH ( ) with wildcard

[DashboardNovice]

The columns in yellow is my dataset. In the blue cells, I have my formula. I am trying to extract cases where column D = DELIVER TO STATE 1 or DELIVER TO STATE 2 or DELIVER TO STATE 3. I want to leave out values that end with '%.'

The orange cells shows what I am aiming for.

In H5, my SEARCH ( ) is:

SEARCH("DELIVER TO STATE ?",$D$5:$D$27)

After "DELIEVER TO STATE" I have a space character and a ? to represent any one character, which totals to 18 characters, so I'm not sure why my results in the blue cells includes "DELIEVER TO STATE" with a space character and a '%' at the end (which totals 20 characters). I would expect that result if I used a "*" but I used a "?" which is supposed to represent a single character as far as I know.

I also tried 18 "*" characters and I still got the same result.

What is wrong with my wildcard?

 

[AliGW]

Because it's not limiting the search - it's just asking for entries that match the search string plus a space and then one further character - all of the rows have at least this.

You could try this:

=FILTER($B$5:$E$27,(SEARCH("DELIVER TO STATE",$D$5:$D$27)*ISERROR((SEARCH("%",$D$5:$D$27)))))

Please give me some feedback this time instead of leaving the thread hanging. Thanks.

 

[DashboardNovice]

Because it's not limiting the search - it's just asking for entries that match the search string plus a space and then one further character - all of the rows have at least this.

You could try this:

=FILTER($B$5:$E$27,(SEARCH("DELIVER TO STATE",$D$5:$D$27)*ISERROR((SEARCH("%",$D$5:$D$27)))))

Please give me some feedback this time instead of leaving the thread hanging. Thanks.

It worked. Thank you.

 

[AliGW]

What about the VLOOKUP thread???

 

[DashboardNovice]

It worked. Thank you.

When I tried your solution on the dataset I am working on and it didn't work there, but it did in the original file I attached. So I had to modify the FILTER ( ) with this:

=FILTER($B$5:$B$27,--LEFT($B$5:$B$27,1)<>1 * LEN($E$5:$E$27)=18 * IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0))

In this updated file, in I5 I am getting the #CALC! error. I have 3 filter criteria. When I select each of them they seem to work out fine. But when I select the following, I am not getting the right answer for the 2nd row of the array.

--LEFT($B$5:$B$27,1)<>1 * LEN($E$5:$E$27)=18

For the 2nd row of the array, both of these evaluate to TRUE and TRUE, but when I select both of these criteria together, all of a sudden the 2nd row of the array evaluates to FALSE. See the attached pic.

 

[AliGW]

What about the VLOOKUP thread?

VLOOKUP - round up to next number

Column I shows the results I am trying to get. My formula is in column G. I am doing a VLOOKUP with column F as the lookup value. I want to look in column O. If the lookup value is not found in column O, VLOOKUP returns the first number below the lookup value, but I would like for VLOOKUP to...

chandoo.org

Happy to look, but I really would like some feedback in that other thread, which is still hanging ...

 

[AliGW]

Try this:

=FILTER($B$5:$F$27,(--LEFT($B$5:$B$27,1)<>1) * (LEN($E$5:$E$27)=18) * (IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0)))

Note yours was missing all the requisite parenthesis.

 

[DashboardNovice]

Try this:

=FILTER($B$5:$F$27,(--LEFT($B$5:$B$27,1)<>1) * (LEN($E$5:$E$27)=18) * (IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0)))

Note yours was missing all the requisite parenthesis.

Right before I checked back on this, I put the parenthesis like you did, except I had another "--" before the opening parenthesis, but I like your solution better.

On the dataset I'm working on, I used CHOOSECOLS and selected the 3rd column, and I'm getting the #SPILL! error, but I selected all cells below the formula down to the very last one in the entire spreadsheet and hit delete to clear out any obstructions, and I'm still getting the #SPILL! for some reason.

 

[AliGW]

Workbook?

And a reply in the VLOOKUP thread, please. If I don’t get one, I’m out. I’m finding a lack of one extremely discourteous.

 

[DashboardNovice]

Workbook?

And a reply in the VLOOKUP thread, please. If I don’t get one, I’m out. I’m finding a lack of one extremely discourteous.

I realized that I have another problem that I have to fix first, then that might clear up the problem about the spill error somehow.

For any number in column D (level 1), if '1' appears in column C (Level No), then all of those should be excluded. For example, all the rows in orange should be excluded because there is a level 1 in C5.

I thought that this part might handle that but I guess not.

(--LEFT($B$5:$B$27,1)<>1)

It looks like this part is excluding B5:F5, instead of B5:F17.

My formulas in the blue include all the '00399' numbers but it shouldn't. I thought that concatenating columns C:D might take care of this.

The orange cells show what I was looking for.

 

[AliGW]

I realized that I have another problem that I have to fix first, then that might clear up the problem about the spill error somehow.

Not a reason to leave your helper without any feedback at all - again, a lack of basic courtesy, sadly, especially when you've been prompted multiple times for a comment.

Anyway, you are still lacking parenthesis. This:

=FILTER($B$5:$F$27,(--LEFT($B$5:$B$27,1)<>1) * LEN($E$5:$E$27)=18 * IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0))

should be this:

=FILTER($B$5:$F$27,(--LEFT($B$5:$B$27,1)<>1) * (LEN($E$5:$E$27)=18) * (IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0)))

This will get the new set of expected results:

=FILTER(B5:F27,(--LEFT(B5:B27,1)<>1) * (LEN(E5:E27)=18) * (IFERROR(SEARCH("DELIVER TO STATE",E5:E27),0)) * (F5:F27="MD"))

If you keep ignoring the parenthesis issue then you will keep on having problems. Try to learn from the corrections I am making.

 

[DashboardNovice]

Not a reason to leave your helper without any feedback at all - again, a lack of basic courtesy, sadly, especially when you've been prompted multiple times for a comment.

Up until now I have exercised restraint as far as replying to your harsh messages, giving you the benefit of the doubt, just in case that was as one time or two time or even three time thing. That didn't work, so now it's time for me to put my foot down.

I think you are taking this way too far, and your tone, even from previous posts is very harsh and unnecessary. While I do appreciate the help (did you read that?), it's not like I can monitor any thread I post 24/7. Did you know that I have a job? My job is my priority. Surely you can understand that........right? As far as not replying to that VLOOKUP thread, I simply forgot about that. It happens. I repeat, it happens.

If you keep ignoring the parenthesis issue then you will keep on having problems. Try to learn from the corrections I am making.

Again, harsh and unnecessary. It is very easy to overlook things. They call that making a mistake. It happens to everybody, just like how it happened to you when you were learning Excel.

In short, adjust your tone. If you don't want to reply to my posts, then don't reply.

 

[AliGW]

OK - in the light of this, I shall withdraw from the fray.

Forgetting is one thing: completely ignoring requests for feedback is another thing entirely.

My time is precious, too. I'll spend it on other things.

Good luck.

 

[DashboardNovice]

Not a reason to leave your helper without any feedback at all - again, a lack of basic courtesy, sadly, especially when you've been prompted multiple times for a comment.

Are you joking? I found another problem with my spreadsheet so I modified it before reposting. You expect people to answer right away. That's not realistic.