SOLVED

Double "if" conditions to filter rows

Copper Contributor

Hello,

 

i need help with Excel Formulas & Functions after i got a headache :D

I need a formula that basically says "if in row B1 the text XY is present AND in row C1 the text XY is present, then say TRUE".

 

However, it is important that it includes some kind of "contains text", because it is only an excerpt of text that should be identical.

 

After we got that, I want to kind of filter the rows, so that I JUST have the rows with "TRUE".

But it's not possible to just use the default filter that you apply to headlines, because the data is "live" and expands daily, so it has to be sorted by date. I use Supermetrics for this to pull Google Ads data, if that means anything to anyone. In summary: I need to make it work that I ONLY have the rows that contain the same word.

 

For visualization here is a picture:
I only need the green rows, since the red word is identical here.

Bildschirmfoto 2022-06-28 um 16.21.31.png

 

I tried multiple ways to get to my goal but nothing worked like expected.

9 Replies

@LarissaM711 

Will the text you're looking for ALWAYS be the part of column C before the space (or before the word Sale)?

@Hans Vogelaar 

column C will always be "XY Sale".

For column B the "XY" will not always be in the same place

@LarissaM711 

Thanks.

Select A2:D10 (or however far down the data go).

The active cell in the selection should be in row 2, for example A2.

On the Home tab of the ribbon, click Conditional Formatting  > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=ISNUMBER(SEARCH(SUBSTITUTE($C2," Sale",""),$B2))

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

See the attached sample workbook.

@LarissaM711 

 

Hello Larissa,

 

if you're searching for the same text string in both columns use this:

IFERROR(IF(SUM(SEARCH("*string*,Column1:Column2))>0,1,0),0)

if the string is different in each column use:

IFERROR(IF(SEARCH("*XYZ*",Column1)+SEARCH("*ABC*",column2)>0,1,0),0)

SEARCH is not case sensitive, and allows the use of wild cards: "?" is any one character, while "*" are any characters. Placing "*" before inside the quotes before and after the string a will find it anywhere in the cell. 

If TRUE, Search returns a a number >0, representing the number of characters into the cell the string is found. If false it returns #N/A, this is why it checks for errors. 

As written the search returns 1 if found in both, otherwise it returns 0, you can change it to return any value you wish. 

 

Since the table is being updated with new values, you should generate a separate "Results Report" with your filtered results. (Separating the Data, Analysis, and Reporting/Results is a good habit to get into for all your workbooks)).

 

Id recommend a pivot table… then you can sort and filter, and calcualte other values as needed (for example: counting the number of matched lines vs non-matched; grouping lines by dates/periods, with the conversions subtitled for each grouping). If you aren't sure how to create a pivot table, respond in your comments and I'll update with quick instructions. 

 

Hope this helps,

Aaron

@Hans Vogelaar Thanks for your effort!
Do you know a way how I could get the data filtered into a second table? I would like to have the green marked rows, those that contain the same word, as a separate table, so that I have ONLY these rows.

@LarissaM711 

Do you have Microsoft 365 or Office 2021? If so, you can use the Filter function:

 

=FILTER(Sheet1!A2:D10,ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10)))

 

See the modified version attached to this reply.

@Hans Vogelaar oh yeah that works! ... Could you please explain me this function, I don't quite get why this function is working? What does this formula filter for? I see that the formula is a link to the first sheet and includes the columns. But how does this formula filter only the rows that are marked green?
best response confirmed by LarissaM711 (Copper Contributor)
Solution

@LarissaM711 

The syntax of the FILTER function is

 

FILTER(array, condition, [if_empty])

 

The first argument specifies which values you want to filter - for example a range. In the sample workbook, this is the range A2:D10 on Sheet1

 

The second argument specifies the condition. It must evaluate to TRUE for each row to be included, and to FALSE for rows to skip. In the sample workbook, it is basically the same as the formula used in the conditional formatting rule, except that it now applies to the entire range instead of just to the first row.

We use

 

ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10))

 

SUBSTITUTE(Sheet1!C2:C10," Sale","") removes the word Sale from the values in column C on Sheet1 so that we are left with E-Bike or VHV.

SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10) returns the position of that phrase in the value of column B, if found. Otherwise it returns the error #VALUE!

So for row 2, for example, it returns the number 4 since PHV is found starting at the 4th character in B2.

But for row 4, it returns #VALUE! since VHV is not found in B4.

Finally, ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10)) returns TRUE if the value was found, FALSE if not. This is precisely what we need to filter the range.

 

The third argument is optional. It specifies what text to return if none of the rows in array satisfy the condition. I omitted this argument.

@Hans Vogelaar thank you very much! I would never have come up with this solution in my life.
1 best response

Accepted Solutions
best response confirmed by LarissaM711 (Copper Contributor)
Solution

@LarissaM711 

The syntax of the FILTER function is

 

FILTER(array, condition, [if_empty])

 

The first argument specifies which values you want to filter - for example a range. In the sample workbook, this is the range A2:D10 on Sheet1

 

The second argument specifies the condition. It must evaluate to TRUE for each row to be included, and to FALSE for rows to skip. In the sample workbook, it is basically the same as the formula used in the conditional formatting rule, except that it now applies to the entire range instead of just to the first row.

We use

 

ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10))

 

SUBSTITUTE(Sheet1!C2:C10," Sale","") removes the word Sale from the values in column C on Sheet1 so that we are left with E-Bike or VHV.

SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10) returns the position of that phrase in the value of column B, if found. Otherwise it returns the error #VALUE!

So for row 2, for example, it returns the number 4 since PHV is found starting at the 4th character in B2.

But for row 4, it returns #VALUE! since VHV is not found in B4.

Finally, ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10)) returns TRUE if the value was found, FALSE if not. This is precisely what we need to filter the range.

 

The third argument is optional. It specifies what text to return if none of the rows in array satisfy the condition. I omitted this argument.

View solution in original post