finding data in duplicate rows, revising row data and deleting duplicates

Hello, I have a worksheet that has over 4500 rows and about 20 columns of information. A serial number may be listed multiple times. I am looking to compare the information and if duplicated consolidate the duplicated data to one row.

Here's an example of te worksheet:

ColA ColB ColC ColD ColE ColF
07002 E046 NYNJ EAST 19,581
07002 E046 NYNJ EAST 475.30
07002 E046 NYNJ EAST 950.60

End result I would like to see:
ColA ColB ColC ColD ColE ColF
07002 E046 NYNJ EAST 19,581 1425.90

I DO NOT know how to do macros (Yeah, excel dummy) so I need help. PLEASE.

Hi

Create a spreadsheet using your example data.

Put headings in A1:E1 of sheet1, and your example data in A2:E4.
Make sure you have a sheet2 in your workbook.
Put the macro below into a general module and run.

Sub aaa()
  Dim OutSH As Worksheet, WrkSH As Worksheet, DataSH As Worksheet
  Set DataSH = Sheets("Sheet1")
  Set OutSH = Sheets("Sheet2")
  OutSH.Cells.ClearContents
  OutSH.Range("A1:D1").Value = Range("A1:D1").Value
  
  DataSH.Range("A:D").AdvancedFilter action:=xlFilterCopy, copytorange:=OutSH.Range("A1:D1"), unique:=True
  Sheets.Add after:=Sheets(Sheets.Count)
  ActiveSheet.Name = "WorkSheet"
  Set WrkSH = ActiveSheet
  'DataSH.Activate
  WrkSH.Range("A1:E1").Value = DataSH.Range("A1:E1").Value
  WrkSH.Range("F1:I1").Value = DataSH.Range("A1:D1").Value
  
  Set rng = OutSH.Range("A2:A" & OutSH.Cells(Rows.Count, 1).End(xlUp).Row)
  
  For Each ce In rng
    WrkSH.Range("F2:I2").Value = ce.Resize(1, 4).Value
    DataSH.Range("A:E").AdvancedFilter action:=xlFilterCopy, criteriarange:=WrkSH.Range("F1:I2"), copytorange:=WrkSH.Range("A1:E1")
    For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row
      OutSH.Cells(ce.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(i, "E").Value
    Next i
  Next ce
  Application.DisplayAlerts = False
  WrkSH.Delete
  Application.DisplayAlerts = True
End Sub

It will add a temporary working sheet which will be deleted, and the output will go into sheet2.

HTH

rylo