Solved: Sum and compile list of entries

[JohnyG]

Hi,

I need to create a macro that will search for duplicate "Registration No" If found then add the "Amount Due" of all the same "Registration No" and show it as a single entry. Later delete the rest duplicate rows. Attached please find the sample.

Regards,

[Simon Lloyd]

In your example you only have 1 set uf duplicates for registration number 5927557 but they have different company names!, what is it that you are trying to achieve?

[JohnyG]

Hi,

If you really see in sheet1 (Data) you will find multiple occurence of Registration no "7778987" where in sheet2 (Output) you will find only one occurence for the same. However, if you see the Amount Due in sheet 2 for registration number "7778987" it is the total of all the amount due in sheet1.

Actually sheet2(Output) is the sample output that I want basis the data available in Sheet1(Data). Please see the color coded rows it will give u better understanding.

Thanks and Regards,

[Simon Lloyd]

This will take all your data from the DATA sheet and do what you want placing the results in the Output sheet:

Sub Find_Dups_Sum_To_New_Sheet()
Dim MyData As Worksheet, MyOutput As Worksheet
Dim Lrow As Long, LrowDst As Long
Dim Rng As Range
Set MyData = Worksheets("Data")
Set MyOutput = Worksheets("Output")
MyOutput.Cells.ClearContents
Lrow = MyData.Range("A" & Rows.Count).End(xlUp).Row
MyData.Range("A1:A" & Lrow).AdvancedFilter xlFilterCopy, , MyOutput.Range("A1"), True
MyOutput.Range("B1") = "Amount Due"
LrowDst = MyOutput.Range("A" & Rows.Count).End(xlUp).Row
MyOutput.Range("B2:B" & LrowDst) = "=SUMIF(Data!$A$2:$A$200,A2,Data!$C$2:$C$200)"

End Sub

[JohnyG]

Thanks Simon,

It worked perfectly..

Thanks and Regards