Macro to filter based on identical column data as opposed to entire record

**marielb** · 08-27-2007, 10:13 AM

Hello all,

I'm relatively unfamiliar with macros and in need of assistance. I have a massive data set that we know includes many duplicates BUT the syntax for the data in certain fields does not necessarily identically match the same field in the duplicate record, thus the basic sort function is not working adequately. So I am trying to remove duplicate records based on identical data in the first 2 of 5 columns in the dataset. It is set up like this:

A (Artist) B (Title) C (Claimant) D (SR #) E (Source)

I am looking for a macro that will identify and remove duplicate records based on records with identical data in columns A and B.

Any ideas? Thanks so much for any help you can provide.

**FrankBoston** · 08-27-2007, 12:27 PM

The following approach gives you flexibility without installing or modifying macros. Column DupNxt identifies records that are duplicated in the next row. Column Dup adds those records that have a duplicate in the prior row.

If you select columns DupNxt and Dup, Copy, PasteSpecial:Values (in place), you freeze those values and can sort your data into order by duplicates. After inspection for unusual cases, you can delete all the 1's, keeping only the "last duplicate" 2's.

The DupNxt formula is easy to change if you notice anomolies in your data, or want to apply different tests for what a "duplicate" is.

Tell me if this helps.

 A    B        C        D        E
      Author   Title   DupNxt   Dup
                            
 4    Jim      T1       1        1
 5    Jim      T1       0        2
 6    Jim      T2       0        
 7    Jim      T3       1        1
 8    Jim      T3       0        2
 9    Mike     T3       0        
10    Mike     T4       0        
11    Mike     T5       0        
12    Mike     T6       0        
13    Mike     T7       0        
14    Dave     T8       0        
15    Dave     T9       1        1
16    Dave     T9       1        1
17    Dave     T9       0        2
18    Dave     T10      0        
19    Dave     T11      0        
                                
      D4  =--AND(B4=B5,C4=C5)      1 if the next record is a duplicate
      E4  =IF(D4,1,IF(D3,2,""))    1 if a duplicate, 2 if the last duplicate