Giving duplicates in Excel a unique identifier

Question

I have a large excel spreadsheet containing multiple address lines. Many of these adress lines contain duplicate postcodes, with differing steet adrresses.

I need to seperate out each of these duplicates onto different spreadhseets, so that no one spreadhseet contains the same postcode. I'm currently using an =if() statement to identify the duplicates, then cutting and poasting, and repeating almost ad infinitum to speperate them all out

as an example:

address 1 postcode 1
address 2 postcode 1
address 3 postcode 1
address 4 postcode 2
address 5 postcode 2
address 6 postcode 3
address 7 postcode 3
address 8 postcode 3
address 9 postcode 3

would give 4 spreadsheets:

1)
address 1 postcode 1
address 4 postcode 2
address 6 postcode 3
2)
address 2 postcode 1
address 5 postcode 2
address 7 postcode 3

etc

Any hints on a vb script?

Many thanks

Nick

Answer 1

If this is a one-off thing, then I'd consider doing it in the worksheet and bulk cut-pasting. I'd try something like this:

1. Sort my data on the post code column
2. In a new column, add a function to count the number of post codes below the current row that are duplicates of the code on that row (see below)
3. Save (copy/paste-special-values) the values obtained
4. Sort by duplicate number
5. Cut and paste blocks by duplicate number to new sheets

Say I have 20 values between 1 and 5 in column G. I put the following formula in H1:

=SUM(IF(G1:$G$20=G1,1,0))

... and (important bit) enter it as an array formula using Control+Shift+Enter. Now I copy that formula down (for speed, double-click the "fill handle" on the bottom right-hand corner of the cell). Here's my result:

1   2
1   1
2   2
2   1
3   8
3   7
3   6
3   5
3   4
3   3
3   2
3   1
4   2
4   1
5   6
5   5
5   4
5   3
5   2
5   1

Now I can copy column H and Edit...Paste Special...Values to fix the values. Sort by H to get this:

1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2
4   2
5   2
3   3
5   3
3   4
5   4
3   5
5   5
3   6
5   6
3   7
3   8

Now I have sets of records to copy to each of the 8 sheets that I now know are needed.

Of course, if you're going to be doing this a lot, then a macro would be useful. It needn't be much more complex than automating the above.