Hi
This assumes that your data is in sheet1, and the output is going to sheet2 which exists, but is blank.
Sub aaa()
Dim OutSH As Worksheet
Dim nodupesLOB As Collection, nodupesEMC As Collection
Set OutSH = Sheets("Sheet2")
OutSH.Range("A1:C1").Value = Array("RptLOB", "EMCAccount", "Amount")
Set nodupesLOB = New Collection
Set nodupesEMC = New Collection
On Error Resume Next
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
nodupesLOB.Add Item:=Cells(i, 1), Key:=Cells(i, 1)
Next i
For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
nodupesEMC.Add Item:=Cells(1, i), Key:=Cells(1, i)
Next i
For i = 1 To nodupesLOB.Count
For j = 1 To nodupesEMC.Count
outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
OutSH.Cells(outrow, 1).Value = nodupesLOB(i)
OutSH.Cells(outrow, 2).Value = nodupesEMC(j)
Next j
Next i
OutSH.Range("C2").Formula = "=SUMIF(Sheet1!A:A,A2,OFFSET(Sheet1!$A:$A,0,MATCH(B2,Sheet1!$1:$1,0)-1))"
With OutSH
.Range("C2").AutoFill Destination:=.Range("C2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
End With
End Sub
Hope that gets you started.
rylo
Bookmarks