Sum Multiple Columns Based on Multiple Criteria

[aread]

I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does not matter. When summing columns G & I, the cell value is only the number even though it also shows the unit of measure when copied and pasted. I have removed our multiple vendors' names from column D due to confidentiality. I am hoping that someone will be able to assist because I keep hitting a brick wall. Thank you in advance.

Material	Plant	Vendor	Vendor Name	Fiscal year/period	Fiscal year	PO quantity	Order value	Actual GR quantity	GR value	Invoiced quantity	Invoiced amount	Var Inv Val vs PO value	Unit Price (Inv)
30000001	2230	1005462		JAN	2017	510,609 LB	$ 146,527.87	466,281 LB	$ 133,598.54	466,281 LB	$ 134,712.33	$ -11,815.54	$ 0.29 / LB
30000001	2230	1005462		JUL	2017	70,408 LB	$ 21,524.74	70,408 LB	$ 21,524.74	70,408 LB	$ 21,798.97	$ 274.23	$ 0.31 / LB
30000001	2230	1005462		OCT	2017	192,092 LB	$ 60,966.21	141,904 LB	$ 45,037.62	141,904 LB	$ 41,016.16	$ -19,950.05	$ 0.29 / LB
30000001	2230	1005462		DEC	2017	71,966 LB	$ 21,829.57	71,966 LB	$ 22,840.69	71,966 LB	$ 20,457.16	$ -1,372.41	$ 0.28 / LB
30000001	2230	1005462		FEB	2018	652,437 LB	$ 208,916.42	651,379 LB	$ 205,397.84	651,379 LB	$ 216,981.38	$ 8,064.96	$ 0.33 / LB
30000001	2230	1005462		NOV	2018	132,000 LB	$ 43,003.62	69,339 LB	$ 22,589.67	69,339 LB	$ 23,734.77	$ -19,268.85	$ 0.34 / LB
30000001	2230	1005462		JAN	2019	850,000 LB	$ 266,149.75	561,657 LB	$ 180,367.64	561,657 LB	$ 169,588.80	$ -96,560.95	$ 0.30 / LB
30000003	2230	1005462		JAN	2017	2,226,648 LB	$ 581,034.26	2,226,648 LB	$ 581,034.26	2,226,648 LB	$ 591,520.58	$ 10,486.32	$ 0.27 / LB
30000003	2230	1005462		AUG	2017	1,354,793 LB	$ 387,965.43	1,302,618 LB	$ 373,024.21	1,302,618 LB	$ 355,510.42	$ -32,455.01	$ 0.27 / LB
30000003	2230	1005462		NOV	2017	504,907 LB	$ 150,477.46	504,907 LB	$ 150,477.46	504,907 LB	$ 132,783.95	$ -17,693.51	$ 0.26 / LB
30000003	2230	1005462		FEB	2018	1,994,822 LB	$ 578,724.53	1,994,822 LB	$ 558,457.98	1,994,822 LB	$ 586,726.22	$ 8,001.69	$ 0.29 / LB
30000003	2230	1005462		OCT	2018	1,138,598 LB	$ 363,406.32	1,097,019 LB	$ 350,135.54	1,097,019 LB	$ 343,453.86	$ -19,952.46	$ 0.31 / LB
30000003	2230	1005462		JAN	2019	1,973,753 LB	$ 583,905.21	1,973,753 LB	$ 583,905.21	1,973,753 LB	$ 561,916.93	$ -21,988.28	$ 0.28 / LB
30000003	2230	1005462		APR	2019	388,151 LB	$ 103,830.39	388,151 LB	$ 103,830.39	388,151 LB	$ 104,320.95	$ 490.56	$ 0.27 / LB
30000003	2230	1005462		NOV	2019	666,426 LB	$ 144,947.66	583,974 LB	$ 127,014.34	583,974 LB	$ 127,014.24	$ -17,933.42	$ 0.22 / LB
30000003	2230	1005462		DEC	2019	2,828,000 LB	$ 615,090.00	505,784 LB	$ 110,008.03	505,784 LB	$ 104,473.99	$ -510,616.01	$ 0.21 / LB
30000005	2230	1000562		DEC	2017	148,490 LB	$ 39,958.66	84,000 LB	$ 22,604.40	84,000 LB	$ 22,874.00	$ -17,084.66	$ 0.27 / LB
30000005	2230	1005074		NOV	2018	42,000 LB	$ 11,302.20	42,000 LB	$ 11,302.20	42,000 LB	$ 11,809.00	$ 506.80	$ 0.28 / LB
30000005	2230	1005074		APR	2019	106,490 LB	$ 29,465.78	84,000 LB	$ 23,242.80	84,000 LB	$ 23,618.00	$ -5,847.78	$ 0.28 / LB
30000005	2230	1005074		DEC	2019	148,490 LB	$ 42,572.08	0 LB	$ 0.00	0 LB	$ 0.00	$ -42,572.08	X
30000006	2230	1004428		AUG	2017	30,240 LB	$ 14,515.20	15,000 LB	$ 7,200.00	15,000 LB	$ 7,200.00	$ -7,315.20	$ 0.48 / LB
30000006	2230	1004428		NOV	2017	51,260 LB	$ 24,604.80	45,000 LB	$ 21,600.00	45,000 LB	$ 22,600.00	$ -2,004.80	$ 0.50 / LB
30000006	2230	1004428		NOV	2018	60,000 LB	$ 30,600.00	60,000 LB	$ 30,600.00	60,000 LB	$ 31,552.50	$ 952.50	$ 0.53 / LB

 

[aread]

No, I want to keep the formatting and copy it to the newly consolidated data without having to go through the materials one by one to see which unit of measure applies.

 

[DanteAmor]

No, I want to keep the formatting and copy it to the newly consolidated data without having to go through the materials one by one to see which unit of measure applies.

Sorry, but I still don't understand what you need.
Is it part of the macro? Or is it a new requirement?

 

[aread]

I wanted it as part of the same one if possible, but I haven't been able to figure it out.

 

[DanteAmor]

I wanted it as part of the same one if possible, but I haven't been able to figure it out.

But I don't understand what you need.
The data has "ton" in the number like "LB", if so, then change this line:

cad2 = Replace(Replace(Replace(a(i, 7) & "|" & a(i, 8) & "|" & a(i, 9) & "|" & _
           a(i, 10) & "|" & a(i, 11) & "|" & a(i, 12) & "|" & a(i, 13), " LB", ""), " TON", ""), ",", "")

 

[aread]

In the original data, there is different number formatting depending on how the material is weighed such as #,##0" LB";-#,##0" LB";#,##0" LB" or #,##0" TON";-#,##0" TON";#,##0" TON" and I want the summed data to maintain the format of the original data without needing to go line by line manually if possible.

 

[DanteAmor]

Do you want the result of the sums to have the format "#,##0" LB";-#,##0" LB";#,##0" LB" or #,##0" TON";-#,##0" TON";#,##0" " on the "summary" sheet?

You can explain that with examples.

That is, the macro, can you add LB values with TON? or they are in different columns.
That's why I need you to explain it with examples.

If a column is always LB, would it be enough to change the format of the entire column to LB format?
Or how are your original data? because in your example of the initial requirement there are only values with LB; and apparently it is not a format, it is a text.

 

[aread]

It is definitely a format, not just text. When you click on the cell, you only see the number in the formula bar. In each of the columns "PO quantity" "Actual GR quantity" and "Invoiced quantity" there are some cells with "LB" as part of the number format and some with "TON"

Does that help?

 

[DanteAmor]

When summing columns G & I, the cell value is only the number even though it also shows the unit of measure when copied and pasted.

So is it text or format?
Why does your initial example appear as text?

there are some cells with "LB" as part of the number format and some with "TON"

Then the sum can contain values with "TON" and values with "LB" so what format should be put?

 

[aread]

Sorry for the confusion...

So is it text or format?
Why does your initial example appear as text?

It is format. "LB" and "TON" are part of the number format. It pastes the same as if it were formatted as Accounting with the $ even though the $ is not part of the actual number.

Then the sum can contain values with "TON" and values with "LB" so what format should be put?

It should retain the original formatting for the material number. Each material number will only have one type of number formatting for the quantity columns, but the column itself will contain multiple formats for different material numbers.

 

[DanteAmor]

Each material number will only have one type of number formatting

The process will take more time to format the rows.

Try this

Sub Sum_Multiple_Columns()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, j As Long, a As Variant, b As Variant
  Dim cad1 As String, cad2 As String, cad3 As String
  Dim val1 As Variant, val2 As Variant, vSum As Double
  Dim dic As Object, ky As Variant, dic2 As Object
  Dim f As Range, frm As Variant, lr As Long
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("Sheet1")    'source data
  Set sh2 = Sheets("Summary")   'destination
  sh2.Rows("2:" & Rows.Count).ClearContents
  sh2.Range("A1").Value = sh1.Range("A1").Value
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
  a = sh1.Range("A2:M" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Value2
  
  For i = 1 To UBound(a, 1)
    dic2(a(i, 1)) = Empty
    cad1 = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) & "|" & a(i, 6)
    cad2 = Replace(Replace(a(i, 7) & "|" & a(i, 8) & "|" & a(i, 9) & "|" & _
           a(i, 10) & "|" & a(i, 11) & "|" & a(i, 12) & "|" & a(i, 13), " LB", ""), ",", "")
        
    If Not dic.exists(cad1) Then
      dic(cad1) = cad2
    Else
      val1 = Split(dic(cad1), "|")
      val2 = Split(cad2, "|")
      cad3 = ""
      For j = 0 To UBound(val1)
        vSum = Val(val1(j)) + Val(val2(j))
        cad3 = cad3 & "|" & vSum
      Next
      dic(cad1) = Mid(cad3, 2)
    End If
  Next
  
  sh2.Range("A2").Resize(dic.Count).Value = Application.Transpose(dic.keys)
  sh2.Range("A2").Resize(dic.Count).TextToColumns sh2.Range("A2"), _
    xlDelimited, xlTextQualifierDoubleQuote, , , , , , True, "|"
  sh2.Range("E2").Resize(dic.Count).Value = Application.Transpose(dic.items)
  sh2.Range("E2").Resize(dic.Count).TextToColumns sh2.Range("E2"), _
    xlDelimited, xlTextQualifierDoubleQuote, , , , , , True, "|"

  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  For Each ky In dic2
    frm = sh1.Range("A:A").Find(ky, , xlValues, xlWhole).Offset(, 6).NumberFormat
    sh2.Range("A1:K" & lr).AutoFilter 1, ky
    sh2.AutoFilter.Range.Range("E2:E" & lr & ",G2:G" & lr & ",I2:I" & lr).NumberFormat = frm
  Next
  sh2.ShowAllData
End Sub