Filtering out unwanted duplicate records

**troydk** · 09-02-2008, 08:14 AM

Hi,

I have been given some test result extract data from a database and am trying to find a way of filtering out some of the data that I do not need.

As you can see in the example below, test 111111 was run twice in Cycle 1, but all that I am concerned about is the detail of the latest run.

Column A Column B Column C Column D
1 Test No. Cycle Status Test Id
2 111111 Cycle 1 Passed 885654
3 111111 Cycle 1 Failed 128766
4 111111 Cycle 2 Passed 888900
5 222332 Cycle 1 Passed 778787

I am thinking that Advanced Filter might be the solution and would like to be able to filter the data that I want to another table. Unfortunately I am not sure how to achieve this.

My logic would be:
If a test number has occurs more that once in column A, and there is more than one occurance of a matching cycle is column B, then only display the record with the greatest Test Id. ie. 885654.

Sorry if this is a little confusing, but any help would be appreciated.
Am using MS Office 2003.

Thanks
Troy

**NBVC** · 09-02-2008, 08:22 AM

I think you need to first add a helper column that tells you which rows are candidates for filtering...

If you sample set is in A2:D5, then in E2 use formula:

=D2=MAX(IF($A$2:$A$5=A2,IF($B$2:$B$5=B2,$D$2:$D$5)))

adjust the ranges to ensure all data is captured.

Then you must confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

Then copy down.

TRUE results mean this is the Maximum for the combination in Columns A & B.

Then you can Autofilter for TRUEs and, if desired, copy/paste elsewhere.

**troydk** · 09-03-2008, 06:44 AM

Thanks very much for the help NBVC.
I tried your suggestion and it works a treat.

One other question though if I may. On Sheet 2 I have created some Pivot Tables to display the results of this data.

Without having to manually copy/paste the filtered data to a new location every time the results change, is there any way for me to make the Pivot Table look at only the rows with the TRUE value.

Thanks again.
Troy

**NBVC** · 09-03-2008, 07:47 AM

If you include the TRUE/FALSE test in your pivot table rows, you can filter there for TRUE only.

**troydk** · 09-04-2008, 07:21 AM

Thanks again NBVC.
I ended up creating a small macro which autofilters to display True values, then copies the data to a new tab. That was I am linking my Pivot tables to clean data.

Thanks very much for all your help.
Troy