# Extracting Similar Data

#### jmathew

##### Board Regular
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
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
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

#### jmathew

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

#### jmathew

##### Board Regular
I tried the formula and everything is returning a blank.

#### erik.van.geit

##### MrExcel MVP
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

Replies
0
Views
529
Replies
1
Views
426
Replies
0
Views
308
Replies
4
Views
333
Replies
1
Views
138

1,171,585
Messages
5,876,309
Members
433,193
Latest member

### 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.

### Which adblocker are you using?

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

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