How to reorder rows so specific strings are evenly distributed down worksheet

[jaypoole]

Background
I have a worksheet with over 50,000 rows.
I am moving CRM systems and exported them from a cloud based CRM system that contains customer data including email addresses.
Quite a few of the email addresses are old so that the mailboxes don't exist.

I am running these email addresses through a validation tool by importing a csv file into the tool. The tool works fine when it is validating very random email addresses. The problem is it's quite slow when testing batches of email addresses that have emails from the same domains bunched together. If it tests an email address from one domain it can't retest another email addresss from the same domain for 10 seconds as it puts a 10 second delay in between. As mine is a B2C business then gmail/hotmail are quite prevalent so in all parts of the worksheet there are bunched groupings where multiple email domains the same are together - i.e. gmail/hotmail/yahoo etc. As a whole though if they were all distributed evenly then there would hardly be a problem, or if there were then I could leave the outstanding (mainly gmail) address file to the end.

So I want to resequence the worksheet so that an email address from any specific domain will only appear more than 10 rows apart.

1 xxxxxxxx@gmail.com <--------- First instance of a gmail email domain
2 xxxxxxxx@yahoo.com
3 xxxxxxxx@gmail.com <--------- This appears too close to the previous gmail address
4 xxxxxxxx@comcast.com
5 xxxxxxxx@businessdomain.com
6 xxxxxxxx@ntlworld.com
7 xxxxxxxx@gmail.com <---------- As does this
8 xxxxxxxx@gmail.com <---------- and this
9 xxxxxxxx@businessdomain.com
10 xxxxxxxx@ntlworld.com
11 etc etc

Please would someone suggest a method for sorting or redistributing emails so they are split up.

Ideas so far:
Added column that split off the characters in the domain i.e. to the right of the @ character
Copied the values from this column to a new column
Sorted the whole worksheet on this new column
Used a =IF(COUNTIF(A1,"*@gmail*"),"A","") followed by the same with yahoo = B, hotmail=C etc to give an alphabet character.

What I'm stuck on
Now I'd need to know how to put just the As (i.e .gmail) in cells 1, 11, 21, 31, 41 etc. Bs (hotmail) in cells 2, 12, 22, 32, 42 etc and so on.
Cells (6, 7, 8, 9, 10), (16, 17, 18, 19, 20), (26,27,28,29,30) etc can be filled with anything except for the ones previously mentioned as most of these are fairly random domains.

Can anyone help with this please?

[xladept]

Could you just sort by the handles? i.e. the characters preceding the @ i.e. the whole e-mail?

I would think that that should approximate a fairly random distribution.

[jaypoole]

Unfortunately it doesn't because of the high proportion of gmail, hotmail and yahoo email addresses.

[xladept]

I've been playing with this for two days now - at least it scrambles them a little - I can't decrement the index because it makes a forever loop - maybe you can make it work?

Please Login or Register  to view this content.

[jaypoole]

@xladept Thank you so much for taking the time to look at this and try to come up with a solution. I've been trying to get it working but so far to no avail. That is a reflection of my knowledge of VBA, which until this morning was nil, rather than your code!

I've been persevering and can sort of understand the flow of your code above. I just don't have the knowledge at this point to adapt it to solve my problem. I'm not even sure how to run it properly. Nothing seems to happen so I guess I need to make changes to your VBA to fit my worksheet.

What I can understand from your code:
Within the subroutine you've defined the data types for the new column and new row [though I'm confused by having 'r' and 'nc', as well as 'n' and 'nr']
You've started a For loop which runs from the 2nd row all the way to the last row (found by the End(xlUp).Row part) and you set the variable nr to be equal to the row under consideration at that moment in time
If the row number (r) is greater than 2 (which it will be for all except the first instance) then you do another for loop
This for loop runs from the first row in the new column, to the last row and for each considers if

For n = 1 To r - 1
If n > 8 Then Go and do the Process routine. [I'm not sure of why 8 here & generally uncertain what this part is doing)

It looks as though within the process routine cells are cut and inserted into other places but again not knowing VBA then I'm just guessing.

I'm not sure also what is the index. Is this 'n' taht ends up in a forever loop?

Sorry to be a dunce here. I just wanted to acknowledge the time and effort you've put in to trying to help me though.