Extracting Similar Data

jmathew

Board Regular
Joined
Nov 22, 2004
Messages
237
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top