Copy text from column 1, merge with text inside columns 1-120

[SHBeck]

Hello,
I need to merge contents of cells in column 1 with content of cells in columns 2-120, for example,

red 1900 1901 1902 1903 1904 1905 1906 1907 ........ 2018 2019 2020
blue 1915 1916 1917 1918 1919 1920 1921 1922 ........ 2033 2034 2035
green 1906 1907 1908 1909 1910 1911 1912 1913 ........ 2024 2025 2026


needs to be changed to;

red red 1900 red 1901 red 1902 red 1903 red 1904 red 1905 red 1906 red 1907 ....... red 2018 red 2019 red 2020
blue blue 1915 blue 1916 blue 1917 blue 1918 blue 1919 blue 1920 blue 1921 blue 1922 .......blue 1923 blue 1924 blue 1925
green green 1906 green 1907 green 1908........................

Thank you for any help, Shawn

 

[VoG]

Try

Sub Test2()
Dim LR As Long, LC As Long, i As Long, j As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = 1 To LR
    For j = LC To 1 Step -1
        Cells(i, j).Value = Cells(i, j).Value & " " & Cells(i, 1).Value
    Next j
Next i
End Sub

 

[Gerald Higgins]

Hi.

It's not clear what exactly you want.

For example, do you want a SINGLE cell to contain JUST "red 1900" or should it contain "red red 1900 red 1901 red 1902 ......and so on" ?

For basic concatenation, use the & symbol like this

=A1&A2
which would concatenate the values in cells A1 and A2.

You can also do
=A1&"red"
or
="red "&"1901 "&"red "&"1902 " and so on.

 

[Peter_SSs]

Given that you have quite a few columns, if you also have a lot of rows, speed may be an issue for you.
If so, you could try this version which I believe is (ball-park) 10-15 times faster, even with ScreenUpdating turned off.

Edit: Forgot to mention I have assumed the "merge data" starts on row 2

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Merge_Text()<br>  <SPAN style="color:#00007F">Dim</SPAN> a<br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> Prefix <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row<br>  LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column<br>  <SPAN style="color:#00007F">With</SPAN> Range("A1").Resize(LR, LC)<br>    a = .Value<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>      Prefix = a(i, 1) & " "<br>      <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> LC<br>        a(i, j) = Prefix & a(i, j)<br>      <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    .Value = a<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[SHBeck]

this is the result of first macro;

red red 1900 1901 1902...

the text added to column a but not the rest

 

[VoG]

this is the result of first macro;

red red 1900 1901 1902...

the text added to column a but not the rest

This was my result of testing

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	red red	1900 red	1901 red	1902 red	1903 red	1904 red	1905 red	1906 red	1907 red	2018 red	2019 red	2020 red
2	blue blue	1915 blue	1916 blue	1917 blue	1918 blue	1919 blue	1920 blue	1921 blue	1922 blue	2033 blue	2034 blue	2035 blue
3	green green	1906 green	1907 green	1908 green	1909 green	1910 green	1911 green	1912 green	1913 green	2024 green	2025 green	2026 green
Sheet2

 

[SHBeck]

Thank you Peter, that worked perfect!

Also would you show me how to merge the data from column 1 to the right side of the data from the other cells? for example instead of having the finished result being;

red red 1900 red 1901 red 1902

it would show;

red 1900 red 1901 red 1902 red

Thanks again!

 

[Peter_SSs]

Also would you also show me how to merge the data from column 1 to the right side of the data from the other cells? for example instead of having the finished result being;

red red 1900 red 1901 red 1902

it would should;

red 1900 red 1901 red 1902 red

The changes would be to change the 3 Prefix lines to

Dim Suffix As String

Suffix = " " & a(i, 1)

a(i, j) = a(i, j) & Suffix

 

[SHBeck]

Thank you , I tried it again and it worked

 

[SHBeck]

Thank you, You have been great help