Split Husband and Wife into Two rows from one

[Rogeman]

I attach Two sample Databases, the originals containing about 500 People, the first contains Husband and Wife (or Other combinations).

I need to print their Individual Names and other details Etc in a letter, as in the second database. So that I can then do an individual mail merge for each person.

Each person needs their own row entry.

How do I single out the appropriate individual Titles, Initials and Forenames from the original rows and then insert them into the database with the remaining details of surname and address? Creating two rows from the original one.

Is there a way in Excel Formulas or VBA to achieve this?



Roger

 

[snjpverma]

This can certainly be done with the help of a VBA.
Could you provide your data in a copy-able format? so that we don't have to type and prepare the scenario ourselves?

I am not good at VBA but there are lot of experts who can come up with an elegant solution.

You can use the below given XL2BB tool link to present your data in a copyable format.
XL2BB Link

 

[Rogeman]

snjpverma,
Thanks for your reply, i did not realise how I could upload an excel file, I hope it works ok.

Roger

Original listing
Mr & Mrs	T & R	Terence	Ruth	Smith	High Strett	London	Sw1
Mr	J	James		Evans	Main Street	London	Sw2
Mr & Mrs	R & J	Roger	Jennifer	Jones	old Street	London	Sw3
Mr & Mrs	J & A	Jeff	Anne	Brown	New Street	London	Sw4
Mrs	G		Gaynor	Green	Long Lane Short Lane	London	Sw5
Mr & Mrs	D & W	David	Wendy	Blue	Quay street	London	Sw6
Miss	M		Peggy	Red	Hill Street	London	Sw7
Mrs	K		Kathryn	Tellow	Back Lane	London	Sw8
Mr & Ms	A & M	Adrian	Megan	Cramp & Smith	Old Road	London	Sw9
Required Listing
Mr	T		Terence	Smith	High Strett	London	Sw1
Mrs	R		Ruth	Smith	High Strett	London	Sw1
Mr	J		James	Evans	Main Street	London	Sw2
Mrs	J		Ann	Evans	Main Street	London	Sw2
Mr	R		Roger	Jones	old Street	London	Sw3
Mrs	J		Jennifer	Jones	old Street	London	Sw3
Mr	A		Adrian	Cramp	Old Road	London	Sw9
Mrs	M		Megan	Smith	Old Road	London	Sw9
Mr	K		Ken	Tellow	Back Lane	London	Sw8
Mrs	K		Kathryn	Tellow	Back Lane	London	Sw8
Mr	P		Percy	Red	Hill Street	London	Sw7
Miss	M		Peggy	Red	Hill Street	London	Sw7
Mr	D		David	Blue	Quay street	London	Sw6
Dr	W		Wendy	Blue	Quay street	London	Sw6

 

[JLGWhiz]

See if this works for you

Sub t()
Dim c As Range, spl As Variant
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    If InStr(c, "&") > 0 Then
        spl = Split(c, "&")
        Rows(c.Row).Insert
        c.Offset(-1) = Trim(spl(0))
        c = Trim(spl(1))
    End If
    If InStr(c.Offset(, 1), "&") > 0 Then
        spl = Split(c.Offset(, 1), "&")
        c.Offset(-1, 1) = Trim(spl(0))
        c.Offset(, 1) = Trim(spl(1))
        If c.Offset(, 2) <> "" Then
            c.Offset(, 2).Cut c.Offset(-1, 3)
        End If
        c.Offset(, 4).Resize(, 4).Copy c.Offset(-1, 4)
    End If
    If c.Offset(, 2) <> "" Then
        c.Offset(, 2).Cut c.Offset(, 3)
    End If
Next
End Sub

 

[Rogeman]

JLGWhiz,

Thanks your macro worked a treat, but my sample I provided only went to column 'H' but my database actualy goes to column 'T'.
Sorry I did not think of that detail.
The only problem I can see is that on each 'couple' when separated the first one does not have any data after column 'H'
I have tried to understand your code but I am not that confident.
What do I need to add to make it include column 'T'?

Thanks again much appreciated

Roger

 

[JLGWhiz]

Change this line

c.Offset(, 4).Resize(, 4).Copy c.Offset(-1, 4)

To this

c.Offset(, 4).Resize(, 16).Copy c.Offset(-1, 4)

 

[Rogeman]

JLGWhiz,
Thanks for your reply, does that 16 represent the number of columns?

Roger

 

[MARK858]

It is the number of columns from (and including) c offset by 4 columns, in this case c.Offset(, 4) is referring to column E. So it is the number of columns from column E to column T i.e. 16 columns including column E..

 

[Rogeman]

MARK858,
Thanks for your explanation.I have now run the macro and it does exactly what
I wanted.
Thanks again to all who have helped.

Roger

 

[JLGWhiz]

MARK858,
Thanks for your explanation.I have now run the macro and it does exactly what
I wanted.
Thanks again to all who have helped.

Roger

You're welcome and tx to @MARK858 for the input.
Regards, JLG