VBA unique lists onto new sheets

[sh1pley]

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

 

[xld]

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

 

[sh1pley]

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

What about the 2nd, 3rd etc?

 

[xld]

Oops, sorry I missed tha nuance.

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

 

[sh1pley]

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]

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]

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