Merge cells if header matches I have data that is received from multiple sources

Question

Merge cells if header matches

I have data that is received from multiple sources, but most are incomplete. The number of replicates for each sample will vary, but the locations will be constant and singular. Real data will have hundreds of samples across thousands of locations. I would like to combine the data of the same sample and location if the header matches.

Basically, I want to go from this:

to this:

Any suggestiions??

Thank you very muuch,,,

Explanation / Answer

Try this macro - I have assumed that the table starts in A1, and the current region will find all the data, and that there is a header on every column except column A, as shown.

Sub Macro1()
Dim c As Long
Dim h As Range
Dim i As Integer
Dim s As Integer
  
s = 0
Set h = Range("A1").CurrentRegion

c = h.Columns.Count
'Extract the unique headerss
For i = 1 To c
If h.Cells(1, i).Value <> "" Theen
If Application.CountIf(h.Resize(1, i), h.Cells(1, i)) = 1 Then
Cells(1, c + s + 2).Value = h.Cells(1, i).Value
s = s + 1
End If
End If
Next i
  
'Extract the correct values for eeach header and location
Cells(2, c + 2).FormulaArray = _
"=INDEX(RC1:RC" & c & ",MATCH(1,((R1C1:R1C" & c & "=R1C)*(RC1:RC" & c & "<>"""")),FALSE))"
  
Cells(2, c + 2).Copy Cells(2, c + 2).Offset(0, 1).Resize(1, s - 1)
Cells(2, c + 2).Resize(1, s).Copy Cells(2, c + 2).Offset(1, 0).Resize(h.Rows.Count - 2, s)
  
Cells(2, c + 2).CurrentReegion.Value = Cells(2, c + 2).CurrentRegion.Value
h.Offset(0, 1).Resize(1, c).EntireColumn.Delete
  
End Subbb

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects