Merging cells to copy and paste, without using mailmerge

[dyanblak]

Hi , I wonder if anybody can help?
I need to prepare a table that merges names and addresses into one cell so that it can be copied and pasted onto eg a label.
Unable to use mailmerge as not all users are familiar will this so want to keep it simple.
Ideally I would like a formula to use in column I. At the moment I have copied and pasted each cell and entered alt with enter to get the next row, which is obviously time-consuming
Any advice greatly appreciated. Spoof names and addresses used here:

Sign Up Date	Customer Name	Address 1	Address 2	Address 3	Address 4	Address 5	Postcode	To copy and paste into corres, labels etc
19/03/2014	John Smith	56 Tower House	Wandsworth Lane	Lambeth	London		SW6 5ET	John Smith 56 Tower House Wandsworth Lane London SW6 5ET
08/04/2014	Cheryl Jones	3 Kings Road	Ramsgate	Kent			CH11 6LN
07/05/2014	Valerie Patisserie	14 Hill Rise	Haprenden	Hertfordshire			AT5 4SN
12/05/2014	David Evans	27 Bell Road	Retford	Nottingham			DN21 5PF

 

[mart37]

To create een ALT+ENTER you can use: CHAR(10) (Works only when wrap text is active)
=A2&CHAR(10)&B2

 

[sandy666]

you can try Power Query (Get&Transform)

Sign Up Date	Customer Name	Address 1	Address 2	Address 3	Address 4	Address 5	Postcode		Custom
19/03/2014	John Smith	56 Tower House	Wandsworth Lane	Lambeth	London		SW6 5ET		John Smith 56 Tower House Wandsworth Lane Lambeth London SW6 5ET
08/04/2014	Cheryl Jones	3 Kings Road	Ramsgate	Kent			CH11 6LN		Cheryl Jones 3 Kings Road Ramsgate Kent CH11 6LN
07/05/2014	Valerie Patisserie	14 Hill Rise	Haprenden	Hertfordshire			AT5 4SN		Valerie Patisserie 14 Hill Rise Haprenden Hertfordshire AT5 4SN
12/05/2014	David Evans	27 Bell Road	Retford	Nottingham			DN21 5PF		David Evans 27 Bell Road Retford Nottingham DN21 5PF

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Sign Up Date"}, "Attribute", "Value"),
    Group = Table.Group(UOC, {"Sign Up Date"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Value]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

after all you will need to use Wrap Text on result column

 

[Fluff]

Another option using a UDF

Function dyanblak(rng As Range) As String
   Dim x as Variant
   
   x = Filter(Evaluate("index(if(" & rng.Address & "="""",false," & rng.Address & "),1,0)"), False, False)
   dyanblak = Join(x, vbLf)
End Function

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1	Sign Up Date	Customer Name	Address 1	Address 2	Address 3	Address 4	Address 5	Postcode	To copy and paste into corres, labels etc
2	19/03/2014	John Smith	56 Tower House	Wandsworth Lane	Lambeth	London		SW6 5ET	John Smith 56 Tower House Wandsworth Lane Lambeth London SW6 5ET
3	08/04/2014	Cheryl Jones	3 Kings Road	Ramsgate	Kent			CH11 6LN	Cheryl Jones 3 Kings Road Ramsgate Kent CH11 6LN
4	07/05/2014	Valerie Patisserie	14 Hill Rise	Haprenden	Hertfordshire			AT5 4SN	Valerie Patisserie 14 Hill Rise Haprenden Hertfordshire AT5 4SN
5	12/05/2014	David Evans	27 Bell Road	Retford	Nottingham			DN21 5PF	David Evans 27 Bell Road Retford Nottingham DN21 5PF
Main

Cell Formulas
Range	Formula
I2:I5	I2	=dyanblak(B2:H2)

 

[dyanblak]

To create een ALT+ENTER you can use: CHAR(10) (Works only when wrap text is active)
=A2&CHAR(10)&B2

I LOVE this simple fix! Thank you so much
Without seeming ungrateful, is there a way to get rid of the spaces at appear when there is no text in the cell being merged eg Address 5 on first entry. Happy to use it anyway, if the spaces have to stay.
I am getting:

John Smith 56 Tower House Wandsworth Lane Lambeth London SW6 5ET

 

[dyanblak]

you can try Power Query (Get&Transform)

Sign Up Date	Customer Name	Address 1	Address 2	Address 3	Address 4	Address 5	Postcode		Custom
19/03/2014	John Smith	56 Tower House	Wandsworth Lane	Lambeth	London		SW6 5ET		John Smith 56 Tower House Wandsworth Lane Lambeth London SW6 5ET
08/04/2014	Cheryl Jones	3 Kings Road	Ramsgate	Kent			CH11 6LN		Cheryl Jones 3 Kings Road Ramsgate Kent CH11 6LN
07/05/2014	Valerie Patisserie	14 Hill Rise	Haprenden	Hertfordshire			AT5 4SN		Valerie Patisserie 14 Hill Rise Haprenden Hertfordshire AT5 4SN
12/05/2014	David Evans	27 Bell Road	Retford	Nottingham			DN21 5PF		David Evans 27 Bell Road Retford Nottingham DN21 5PF

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Sign Up Date"}, "Attribute", "Value"),
    Group = Table.Group(UOC, {"Sign Up Date"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Value]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

after all you will need to use Wrap Text on result column

Thanks for this - the result looks perfect but I am after the simplest possible way to do it and am favouring Mart 37s way at the moment, although it does create unwanted line spaces.
I am not very au fait with Power Queries etc and have up to 10 address per customer to amalgamate, so a formula would work best for me.
Thank you so much for you input though - very impressed.

 

[dyanblak]

Another option using a UDF

Function dyanblak(rng As Range) As String
   Dim x as Variant
  
   x = Filter(Evaluate("index(if(" & rng.Address & "="""",false," & rng.Address & "),1,0)"), False, False)
   dyanblak = Join(x, vbLf)
End Function

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1	Sign Up Date	Customer Name	Address 1	Address 2	Address 3	Address 4	Address 5	Postcode	To copy and paste into corres, labels etc
2	19/03/2014	John Smith	56 Tower House	Wandsworth Lane	Lambeth	London		SW6 5ET	John Smith 56 Tower House Wandsworth Lane Lambeth London SW6 5ET
3	08/04/2014	Cheryl Jones	3 Kings Road	Ramsgate	Kent			CH11 6LN	Cheryl Jones 3 Kings Road Ramsgate Kent CH11 6LN
4	07/05/2014	Valerie Patisserie	14 Hill Rise	Haprenden	Hertfordshire			AT5 4SN	Valerie Patisserie 14 Hill Rise Haprenden Hertfordshire AT5 4SN
5	12/05/2014	David Evans	27 Bell Road	Retford	Nottingham			DN21 5PF	David Evans 27 Bell Road Retford Nottingham DN21 5PF
Main

Cell Formulas
Range	Formula
I2:I5	I2	=dyanblak(B2:H2)

Thanks for this - the result looks perfect but I am after the simplest possible way to do it and am favouring Mart 37s way at the moment, although it does create unwanted line spaces.
I am not very au fait with Power Queries etc and have up to 10 address per customer to amalgamate, so a formula would work best for me.
Thank you so much for you input though - very impressed.

 

[Fluff]

As you are using 2016 you don't have the textjoin function, so to do it like mart37 showed, you would need multiple ifs along the lines of

=B2&IF(C2="","",CHAR(10)&C2)&IF(D2="","",CHAR(10)&D2)

 

[sandy666]

I am not very au fait with Power Queries etc and have up to 10 address per customer to amalgamate

this is not a problem with much more addresses
the same M code works for 2 or 200 addresses

 

[dyanblak]

As you are using 2016 you don't have the textjoin function, so to do it like mart37 showed, you would need multiple ifs along the lines of

=B2&IF(C2="","",CHAR(10)&C2)&IF(D2="","",CHAR(10)&D2)

PERFECT!