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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,171,585
Messages
5,876,309
Members
433,193
Latest member
BruxoTrader

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