Merge Same Column Name

[christian2016]

Hi Guys,

Need help on how to merge same column names with data.

Example
City | State | Address | Address | Address

Now I would like to loop through the column headers and if finds the same column header name in this example would be address it will combined all the text information in all columns with spaces in-between.
All information will be stored in one column called address and the other 2 address columns will be deleted.

Unsure on how to approach this to make something dynamic.

Any help is greatly appreciated.

Thanks

 

[christian2016]

Having another issue now. Didn't pick it up before maybe because I was using the same data set.

Example

View attachment 56179

View attachment 56180

Tried to go through the code to try and fix it. Haven't been successful

Its this line causing the issues

.Value = Evaluate("trim(" & .Address & "&"" ""&" & .Offset(, cc - j).Address & ")")

 

[Peter_SSs]

Having another issue now. Didn't pick it up before maybe because I was using the same data set.

Example

What is "the issue"?

Is that bottom image what you are getting or what you want to get?
If it is what you are getting, what do you want to get?

 

[christian2016]

What is "the issue"?

Is that bottom image what you are getting or what you want to get?
If it is what you are getting, what do you want to get?

So basically any column header that has the same name I need to combined the column with the row data with a space between the combined data.

 

[Peter_SSs]

If that image is what you want then as far as I can see, the _v2 code does that.

Here is my sample sheet before the v2 code ..

christian2016.xlsm
	A	B	C	D	E
1	Person	Matrix	Matrix	Matrix	Colour
2	Male	12	45	89	Blue
3	Male				Purple
4	Female	65	45	42	Pink
New

.. and after the v2 code

christian2016.xlsm
	A	B	C	D	E
1	Person	Matrix	Colour
2	Male	12 45 89	Blue
3	Male		Purple
4	Female	65 45 42	Pink
New

 

[christian2016]

If that image is what you want then as far as I can see, the _v2 code does that.

Here is my sample sheet before the v2 code ..

christian2016.xlsm
	A	B	C	D	E
1	Person	Matrix	Matrix	Matrix	Colour
2	Male	12	45	89	Blue
3	Male				Purple
4	Female	65	45	42	Pink
New

.. and after the v2 code

christian2016.xlsm
	A	B	C	D	E
1	Person	Matrix	Colour
2	Male	12 45 89	Blue
3	Male		Purple
4	Female	65 45 42	Pink
New

for some strange reason when i use the V2 code i dont get that result. I get the same values from the first row in all the merged column rows

 

[christian2016]

Sub Combine_Columns()

Application.ScreenUpdating = False
  
  lCol = Cells(1, Columns.Count).End(xlToLeft).Column
  lRow = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  For c = 1 To lCol
  
    Heading = Cells(1, c).Value
    NextHeading = Cells(1, c + 1).Value
    
    If IsEmpty(Heading) And IsEmpty(NextHeading) Then
    Exit Sub
    End If
  
        If Heading = NextHeading Then
            StartCol = c
                      For R = 2 To lRow
                              If Heading = NextHeading Then
                              Cells(R, c) = Trim(Cells(R, c) & " " & Cells(R, c + 1))
                              End If
                      Next R
                      
                      EndCol = StartCol + 1
                      c = c - 1
                      Columns(EndCol).EntireColumn.Delete
              
          End If
    Next c


End Sub

I wrote this code and it works although its looping the values.

 

[Peter_SSs]

for some strange reason when i use the V2 code i dont get that result.

Are you sure that you did not modify the code in any way to try to suit your individual circumstances?

Can you provide that sample data with XL2BB to ensure I am working with exactly the same sample data and layout as you?
Failing that, can you upload the sample file, with the v2 code included to DropBox or OneDrive/Google Drive etc and provide a shared link here?