Thread: Formula to find records based on other values in group

Allow me to explain through example:

Here is some sample data:

ID----------GROUP----------STATUS
043----------38---------------Closed
048----------38---------------Open
075----------42---------------Closed
075----------42---------------Closed
088----------53---------------Open
023----------53---------------Closed
015----------53---------------Open

Basically, I only want to retrieve records for a Group if the Group contains at least one Open and at least one Closed for the status column.

I'd be grateful if anyone had any ideas on how to write such a formula.

Assuming your data is in Sheet1, starting with headers at A1...

Then in D2 enter formula:

=IF(AND(COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,"Closed"),COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,"Open")),COUNT($D$ 1:$D1)+1,"")

copied down

and in E2 enter:

=MAX(D:D)

then in Sheet2, A2 use formula to retrieve the relevant info:

=IF(ROWS($A$2:$A2)>Sheet1!$E$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$2:$A2),Sheet1!$D:$D)))

copied down and across 3 columns

So you want to extract all records if there is at least one status that is not "OPEN"?

If yes, try changing formula in D2 of Sheet1 to:

=IF(COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,"<>Open"),COUNT($D$1:$D1)+1,"")

copied down

Then:

=IF(AND(COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,"Open"),COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,"<>Open")), COUNT($D$1:$D1)+1,"")