Extracting Similar Data

[jmathew]

I have a workbook which is about 30000 rows and has columns A through AD filled in with data. Column F contains a name and column J contains a date. I would like to extract the entire row of data if there are duplicates in column F and J; i.e. jane smith is listed four times in column F and in column J the date 1/1/00 appears three times. Therefor I would like those three rows to be transferred to another workbook. Can this be done?

 

[erik.van.geit]

Hi,

in this sample, you can see how the sumproduct-formula generates a column wich marks the items which are duplicate in both columns together

   A       B       C  
 1 header1 header2    
 2 AAA     ABC     1  
 3 AAA     BBB        
 4 AAA     ABC     1  
 5 AAA     CCC        
 6 AAA     DDD        
 7 BBB     MMM        
 8 BBB     NNN        
 9 BBB     OOO        
10 CCC     XYZ     1  
11 AAA     ABC     1  
12 AAA     FFF        
13 BBB     PPP        
14 CCC     XYZ     1  
15 CCC     ABC        
16 CCC     XYZ     1  
17 CCC     DDD        

test
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
C2:C17  =IF(SUMPRODUCT(--($A$2:$A$17=A2),--($B$2:$B$17=B2))>1,1,"")

[Table-It] version 06 by Erik Van Geit

this way you can filter and copy the range

would that do ?

kind regards,
Erik

 

[jmathew]

Do I add this code to the workbook or in vb code?

 

[jmathew]

I tried the formula and everything is returning a blank.

 

[erik.van.geit]

I tried the formula and everything is returning a blank.

two possible causes
1. there are no duplicates (which will probably not be the case)
2. you didn't use it the right way