Filter duplicate entry with condition w excel [Solved/Closed]

Report
-
rogersee
Posts
1
Registration date
Monday March 29, 2010
Status
Member
Last seen
March 30, 2010
-
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

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3930 users have said thank you to us this month

rogersee
Posts
1
Registration date
Monday March 29, 2010
Status
Member
Last seen
March 30, 2010

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