Filter Detail

Copper Contributor

If I have 2 columns. In column 2, a value may repeat multiple times, ex. "1234" is found 5 or 6 times, doesn't matter. I want to know if column 1 has value "ABCD" and "EFGH" on the same rows as "1234" exists in column 2. 

A more accurate example would be column 2 has Producer A. I want to know if Producer A has, in column 1, "Cars" and on a different line "Trucks". 

Things to note"

- The table cannot be rearranged.

- The table is always changing in length, sometimes 5 rows, sometimes 1000

- I would settle with highlighted rows, auto filter, etc. 

 

Please forgive if I'm not clear enough. 

1 Reply

Hi Mark,

 

Please try this formula and find it in the attached workbook:

=IF(AND(SUMPRODUCT(ISNUMBER(MATCH(E2:E3,IF(D2=B2:B9,A2:A9),0))+0)=COUNTA(E2:E3),COUNTA(E2:E3)<>0),"Yes","No")

 

A screenshot.png

 

The formula in the above screenshot is located in cell F2 and it gives you (No) as a result because the Producer A has no corresponding value called (Buses)!

But if you change the value of (Buses) in cell E3 to (Trucks), the formula will return (Yes).

 

Please note that the formula is an array formula so you have to press Ctrl+Shift+Enter to enter this formula each time you open it in the edit mode in order to get the correct result.

 

Hope that helps

Regards