CODEWITHSUNDEEP
excelexcel-formula
Jeffrey Yap
Jeffrey Yap

Reputation: 5

Highlight/extract rows that consist of a specific value

Im not sure if this can be done in excel but here goes.

Im looking for a way to either extract/highlight a list of OrderID & with its related products only if the OrderID has Product "A" while ignoring the other orderID's that does not have A.

I have tried using IF statements to detect Product "A" but it could not check the subsequent OrderID rows. I also thought of using Concatenate/Textjoin but couldn't make it work.

Screenshot of sample data:

enter image description here

Result im trying to achieve

Screenshot of result data

It should only highlights/extract the OrderID's 6613,7557 and 3396 (along with the other products values) as these OrderID's has product "A". While it ignore other OrderIDs that does not have Product "A" (eg. 4519,7601,2113,9880)

Edited: attached 2 pictures to differentiate sample and results

Upvotes: 0

Views: 101

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have O365:

F2: =FILTER($B$2:$C$17,COUNTIFS($B$2:$B$17,$B$2:$B$17,$C$2:$C$17,"A"))

If you have an earlier version of Excel:

=INDEX(B2:C17,
          -1+AGGREGATE(
                       15,6,
                       1/COUNTIFS($B$2:$B$17,$B$2:$B$17,$C$2:$C$17,"A")*ROW(B2:B17),
                        ROW(INDEX($A:$A,1):INDEX($A:$A,SUM(COUNTIFS($B$2:$B$17,$B$2:$B$17,$C$2:$C$17,"A"))))),
            {1,2})

enter image description here

Highlighting the A's can be done with simple conditional formatting (eg cell equals A)

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

To highlight use below formula in conditional formatting.

=AND(ISNUMBER(MATCH($A2,$E$2:$E$10,0)),$B2="A")

enter image description here

If you want to filter data with Product A then use below formula.

=IFERROR(INDEX($A$1:$B$17,AGGREGATE(15,6,ROW($A$1:$A$17)/($B$1:$B$17="A"),ROW(1:1)),COLUMN(A$1)),"")

If you have Office365 then use Filter() formula.

=FILTER(A1:B17,B1:B17="A")

Upvotes: 1

Related Questions