# VBA unique lists onto new sheets

#### sh1pley

##### Board Regular
Hi I have 11 columns of data and about 22'000 rows. In column A I have a list of email addresses of which there are many duplicates.

What I need to do is when I find the 2nd incidence of the email address I need to move/copy that row of data onto sheet 2. When I find the 3rd incidence I need to move/copy that row to sheet 3 and so on

I did a countif of the email addresses and the maximum number of incidences of one email address is 43

Can anyone help me with the code I need to use to automate this?

I hope this is clear

Cheers

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### xld

##### Banned
Use Data>Filter>Advanced Filter with the copy unique option with the macro recorder, that will generate the code for you.

#### sh1pley

##### Board Regular
Wouldnt that just give me the first incidence of each email address though?

What about the 2nd, 3rd etc?

#### xld

##### Banned
Oops, sorry I missed tha nuance.

You could add a column with a formula identifying those, filter it, and copy the filtered list.

#### sh1pley

##### Board Regular
Thats what I started to do manually - but there are upto 43 duplicates of the same email address so this is going to take a long time to do

Is there a formula that can calculate the first incidence as 1, the second as 2 etc

What I have done so far is sort by email address then said if(A2=A1,"Duplicate","") But this just tells me its a duplicate and not what sheet I should put it into?

#### sh1pley

##### Board Regular
Its ok I've solved it. A countif but the range isnt static - so it counts the incidences above it

Cheers for the help though

#### xld

##### Banned
Thats what I started to do manually - but there are upto 43 duplicates of the same email address so this is going to take a long time to do

Is there a formula that can calculate the first incidence as 1, the second as 2 etc

What I have done so far is sort by email address then said if(A2=A1,"Duplicate","") But this just tells me its a duplicate and not what sheet I should put it into?

This will identify the 2nd incidence among many

=COUNTIF(A2:\$A\$1000,A2)=COUNTIF(\$A\$2:\$A\$1000,A2)-1

Replies
1
Views
156
Replies
1
Views
153
Replies
7
Views
459
Replies
1
Views
870
Replies
0
Views
104

1,191,287
Messages
5,985,756
Members
439,979
Latest member
alekun86

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