copy columns and summing based on column with ignore the others

[Mussa]

hi
I need macro to merge data based on column B and sum the values in column H but the column I should calculate average price and column J =column i x column h
with ignores columns C,D with considering my data are about 2500 rows

orginal data

KarmaTest1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	4/1/2021	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140.00	1,400.00
3	4/2/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200.00	2,400.00
4	4/3/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130.00	2,080.00
5	4/4/2021	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146.00	1,460.00
6	4/5/2021	A03	BlS1/12-025	CC-MMSN1/16	AC-AD2	AA-101	TTY2	12	200.00	2,400.00
7	4/6/2021	A04	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144.00	2,304.00
main

Cell Formulas
Range	Formula
J2:J7	J2	=I2*H2

expected result

KarmaTest1.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	CODE	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	1	A02	AC-AD1	AA-100	TTY1	20	143.00	2,860.00
3	2	A03	AC-AD2	AA-101	TTY2	24	200.00	4,800.00
4	3	A04	AC-AD3	AA-102	TTY3	32	137.00	4,384.00
result

Cell Formulas
Range	Formula
H2:H4	H2	=G2*F2

 

[Marc L]

but the column I should calculate average price

How do you calculate it, any sample or formula ?​

 

[Mussa]

Hi Marc L,

for instance A02 in first image =140+146/2=143 if I have three values when repeat the item in column B should sum the three values and divide for numbers count and so on . it depend on numbers count when divide.

 

[Marc L]

Where should be the result : in the original worksheet or in another one like 'Result' ?​

 

[Mussa]

in another one like 'Result' ?

name is result

 

[Marc L]

According to your attachment an Excel basics VBA demonstration to paste to the Result worksheet module :​

Sub Demo1()
    Dim L&, R&, V
        UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
    With [Main!A1].CurrentRegion.Columns
       .AdvancedFilter 2, , [B1], True
        L = UsedRange.Rows.Count:  If L = 1 Then Exit Sub
    For R = 2 To L
       .Rows(Application.Match(Cells(R, 2).Text, .Item(2), 0)).Range("E1:G1").Copy Cells(R, 3)
    Next
        V = .Item(2).Address(, , , True) & ",B2,"
        V = Array("=SUMIF(" & V & .Item(8).Address(, , , True) & ")", _
                  "=AVERAGEIF(" & V & .Item(9).Address(, , , True) & ")", "=F2*G2")
    End With
    With Range("A2:A" & L & ",F2:H" & L).Columns
             .HorizontalAlignment = xlRight
             .IndentLevel = 2
             .Item(1).Value2 = Evaluate("ROW(1:" & L - 1 & ")")
        With .Item("F:H")
             .Formula = V
             .Formula = .Value2
        End With
    End With
        Application.ScreenUpdating = True
End Sub

 

[Mussa]

thanks . it shows "application defined or object defined error" in this line

.AdvancedFilter 2, , [B1], True

and clear my data in sheet MAIN

 

[Marc L]

​

It well works on my side as it is with your attachment so you felt in the bad reader trap !​

Read again post #6 but well this time, just follow the dark red directions …​

 

[Mussa]

ok may be I make mistake but I no know what is . but it doesn't arrange data and not show the numberformat as in sheet main, also delete some borders from some cells.

 

[Marc L]

As it works a treat on my side so where did you paste the procedure ?​

If the name starts with 'Module' then this is very not the Result worksheet module as advised.​

Right click on the Result tab, choose the option about code then paste the procedure there and delete the previous one.​

​

To test in the same condition as mine, open a brand new workbook where you must have two sheets aka Main & Result​

then paste the respective data to each sheet from your original post and finally paste the procedure in the Result worksheet module.​

​

After the Dim codeline add this codeline : If Me.Name <>"Result" Then Beep: Exit Sub …​

If an error raises 'cause of the Me statement so this is not a worksheet module.​

If a beep sound arises, this is not the Result worksheet module …​