VBA sum data in certain columns based on common data

[Forrtis]

I have a very similar problem to the one in the link below:

https://www.mrexcel.com/forum/excel...plicate-rows-sum-values-certain-column-4.html

The only difference is the formatting of the data. The unsorted data comes in the following format:

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Blank column[/TD]
[TD]Blank Column[/TD]
[TD]Blank Column[/TD]
[TD]Quantity[/TD]
[TD]Unit price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y1[/TD]
[TD]5[/TD]
[TD]L*M[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]41[/TD]
[TD]X2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y2[/TD]
[TD]15[/TD]
[TD]L*M[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]41[/TD]
[TD]X2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y3[/TD]
[TD]15[/TD]
[TD]L*M[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]23[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y4[/TD]
[TD]5[/TD]
[TD]L*M[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]41[/TD]
[TD]X2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y5[/TD]
[TD]15[/TD]
[TD]L*M[/TD]
[/TR]
</tbody>[/TABLE]


The expected results is similar to the quoted thread, although there are slight differences:

• Columns L and N need to be summed
• Columns I, J and K need to stay blank
• Any number of rows may contain common data in cells belonging to columns G, H and M
  • Values in Column M may be common for two different ID's therefore this column cannot be used as a guide to consolidate the data
  • Either Column G or H can be used to look for common data and then the common rows need to be summed - only summing the columns indicated above
• There are ~100 different ID's to work with so the code needs to be able to handle this

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Blank column[/TD]
[TD]Blank Column[/TD]
[TD]Blank Column[/TD]
[TD]Quantity[/TD]
[TD]Unit price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y1+Y4[/TD]
[TD]5[/TD]
[TD]L*M[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]41[/TD]
[TD]X2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y2+Y3+Y5[/TD]
[TD]15[/TD]
[TD]L*M[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated!
****** id="cke_pastebin" style="position: absolute; top: 573px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 800"]
<tbody>[TR]
[TD]L*M[/TD]
[/TR]
</tbody>[/TABLE]
aNYa</body>

 

[Forrtis]

That works exactly how I planned for the tool to work! Thank you very much, I am new to this forum is there anything I can do to show my appreciation?

 

[MickG]

You're very welcome
If you feel so inclined you could a donation to a charity of your choice.
Regrds Mick

 

[Forrtis]

Hi MickG, I have tried adding a

With Worksheets("worksheet name")

.......statement into this code so that it carries out this code within a specific worksheet, however this brings up errors that I can't seem to get past? Is there a simple line of code that I can add so that this code works on the data within a certain worksheet?

 

[MickG]

Try changing this :-

Set Rng = Range("G2", Range("G" & Rows.Count).End(xlUp))

To this:-
NB:- Notice the Dots in red ???

With sheets("Sheetname")
     Set Rng [SIZE=3][COLOR="#FF0000"][B].[/B][/COLOR][/SIZE]Range("G2", [COLOR="#FF0000"][SIZE=3][B].[/B][/SIZE][/COLOR]Range("G" & Rows.Count).End(xlUp))
end with

 

[Forrtis]

That nearly works, the code looks at data in the specified worksheet but the result is still printed onto the first worksheet within the workbook; not the specified worksheet.

 

[MickG]

Alter last bit of code as below:-
Change Sheet Name and range "A1" to suit.

With sheets("SheetName").Range("A1").Resize(c, 8)
    .Value = Ray
    .Borders.Weight = 2
End With

 

[Forrtis]

Thank you very much, it worked!

One more thing, when there is a gap in the data i.e an empty row (this happens quite often) the code counts the the empty rows as an item and tries to sum up the data. Which means that the result includes an empty row at the top of the list with "0" in QTY and Amount columns. Is there a way to modify this code so it ignores blank rows that exist in-between the actual data that I am trying to sum?

 

[MickG]

Try adding lines in red

For Each Dn In Rng
 [COLOR="#FF0000"][B]If not IsEmpty(dn.value) then 
[/B][/COLOR] If Not .Exists(Dn.Value) Then
        c = c + 1
        For Ac = 1 To 7
            Ray(c, Ac) = Dn.Offset(, Ac - 1)
        Next Ac
        Ray(c, 8) = Dn.Offset(, 5) * Dn.Offset(, 6)
        .Add Dn.Value, c
    Else
        Ray(.Item(Dn.Value), 6) = Ray(.Item(Dn.Value), 6) + Dn.Offset(, 5)
        Ray(.Item(Dn.Value), 8) = Ray(.Item(Dn.Value), 8) + Dn.Offset(, 5) * Dn.Offset(, 6)
    End If
[COLOR="#FF0000"][B]End If [/B][/COLOR]
Next

 

[Forrtis]

The code has thrown up an error today "Subscript out of range".
I have recently added some delete buttons to my workbook which delete a range of data from the worksheet that the code looks at to sum the common rows.
Once deleted the code also re-runs the code in this thread. So everything works fine up until there are no more duplicate rows for the code to sum, at this point the "Subscript out of range" error comes up highlighting the following line of code

Ray(C, Ac) = Dn.Offset(, Ac - 1)

.

Please note: The code also works fine if the first row of the range that the code is looking to sum is blank. Please see attached images for more clarification!

https://imgur.com/a/hjxieYF

 

[MickG]

I note that the code looks for data starting column "G" and your Data start column "I" ???
Can you send a copy of data/results file (fails not fails) Using "Box.com" or "Dropbox.com" as a picture is not very helpful !!
Regrds Mick