Filter duplicate entry with condition w excel

Solved/Closed
Roger See - Mar 29, 2010 at 01:00 AM
rogersee Posts 1 Registration date Monday March 29, 2010 Status Member Last seen March 30, 2010 - Mar 30, 2010 at 12:44 AM
Hi,
I've 2 columns of data. Column A are repeated Test sequence and Column B are Status (OK / Fail).
I would like to extract those test sequence with 3 times repeated "OK" in status and sort by test sequence.

Column A ColumnB
Test 1 OK
Test 2 OK
Test 3 Fail
Test 4 Fail
Test 1 OK
Test 1 OK
Test 2 OK
Test 2 OK

Expected Result will show :

"TEST 1 has Pass with 3 OKs"
"TEST 2 has Pass with 3 OKs"

how can i do that in VBA code in excel 2003.

Thanks.



1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 29, 2010 at 04:02 PM
you can do this
1. Copy the unique values from Col A to a new sheet ( advance filter can help you with it)

2. The paste this formula and drag down =SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10="OK"))

3. Apply filter and delete all values that are not 3
1
rogersee Posts 1 Registration date Monday March 29, 2010 Status Member Last seen March 30, 2010
Mar 30, 2010 at 12:44 AM
Hi, Thanks for the solution.

I've another question:

1) Is there anyway to capture if the "OK" appear only 3 consecutively per test? If "fail" happend to appear during the 3 test, excel will not capture this test.

2) I notice the formula only works for existing column used. is there any command that allows the formula to auto grow the moment i enter a new test on the worksheet?

Thanks and Regards
0