Combining duplicate records into one.

[dcatley]

Hi,

This is really doing my head in!

I am trying to find some macro code to enable me to do the below. Any help with this would be greatly appreciated.

RAW DATA:


ID MATERIAL1 MATERIAL2 MATERIAL3 MATERIAL4 MATERIAL5
1 METBB1
1 METBB1
1 METBB2
2 METBB3
3 METBB1
3 METBB1

REQUIRED DATA:

ID MATERIAL1 MATERIAL2 MATERIAL3 MATERIAL4 MATERIAL5
1 METBB1 METBB1 METBB2
2 METBB3
3 METBB1 METBB1


Please help??

David

[rylo]

Hi

Here goes

Sub aaa()
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) <> Cells(i - 1, 1) Then
      outrow = i
    Else
      Cells(outrow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(i, 2).Value
      Cells(i, 2).ClearContents
    End If
  
  Next i
  
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Len(Cells(i, 2)) = 0 Then Cells(i, 1).EntireRow.Delete shift:=xlUp
  Next i
End Sub

rylo