duplication with lastName split and a twist :)

**Rucas** · 02-21-2007, 04:55 AM

hi all,
Have joined data together so that where column A to B had same data but C had different data we joined C (with an "&" between each value) and deleted the A and B duplicate rows
I now need to do the opposite with a twist
I now need to split it up again but also need to split the name field up further so that last name is separate to other names.

An example of the data in its current form is below
Cell A1= 5
Cell B1= Bellrose Plaza Someplace 4444
Cell C1= Bob Marley & John Lennon
Cell A2= 6
Cell B2= Bellrose Plaza Someplace 4444
Cell C2 = Jack Johnson & Pete Murray

As you can see check for duplication was based on Row A and B

How I need it is:
Cell A1= 5
Cell B1= Bellrose Plaza Someplace 4444
Cell C1= Bob
Cell D1= Marley
Cell A2= 5
Cell B2= Bellrose Plaza Someplace 4444
Cell C2= John
Cell D2= Lennon
Cell A3= 6
Cell B3= Bellrose Plaza Someplace 4444
Cell C3= Jack
Cell D3= Johnson
Cell A3= 6
Cell B3= Bellrose Plaza Someplace 4444
Cell C3= Pete
Cell D3= Murray

Below is the Macro that i used to delete and merge them in the first place
(I added another row into the match also that are not important in the reversal)

Sub DeleteAndMerge()

Dim i As Long
Dim dCol As Long
Dim lrow As Long

'Column to check first (A)
dCol = 1

'Get last row of data, Col A
lrow = Cells(65536, dCol).End(xlUp).Row

'Check each row: lastrow to row 2
For i = lrow To 2 Step -1

'If Col A AND Col B are match
If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) And Cells(i, 4) = Cells(i - 1, 4) Then

'Col C = Concatentate Col C names
Cells(i - 1, 3) = Cells(i - 1, 3) & " & " & Cells(i, 3)

'Delete dupe row
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

My guess would be to count "&" in row C and then insert number of rows accordingly, then duplicate A and B down.
then split C down into seporate rows according to when it finds "&"
then seporate last term from each of those columns into a new column
I know what needs to be done, just need some help to write it

Any help would be greatly appriciated
Appologies for the long description

**antoka05** · 02-21-2007, 07:30 AM

See the attached file... I added a macro that may be what you need.

Regards,
Antonio

**Rucas** · 02-21-2007, 08:20 AM

That is awesome.
Thankyou soo much for that.. you have saved me hours of work

How could i change it though to only split the last word of the "name" (C) cell
eg: Bob Francis Marley in one cell to start with and then "Bob Francis" in one and "Marley" in another (at the moment it splits into a new cell whenever it finds a space

**antoka05** · 02-21-2007, 09:01 AM

See the attached file.

For each name I take the last blank to divide string in two strings to put in column C and D

I hope it's what you need.

Regards,
Antonio