Merge price as average price for data for two sheets.

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
682
Office Version
  1. 2019
Hi,
I would macro to merge price in columns D, H as average price for each brand in columns B,F for SV,STOCK sheets.
I don't need power query at all .


AFTER (2).xlsm
ABCDEFGHI
1ITEMDATECUSTOMERSINV.NOCASEBRANDQTYPRICEBALANCE
2115/06/2023CCF-1000BSTR_23448OUTSANDINGBS 750R16 R230 JAP3.00500.001,500.00
3215/06/2023CCF-1000BSTR_23448OUTSANDINGBS 700R16 R230 JAP2.00400.00800.00
4SUM2,300.00
5115/09/2023CCF-1000BSTR_23449OUTSANDINGGO 1200R20 AZ0026 CHI1.00920.00920.00
6215/09/2023CCF-1000BSTR_23449OUTSANDINGGO 1200R20 AZ0083 CHI1.001,000.001,000.00
7SUM1,920.00
8115/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 G580 JAP9.001,800.0016,200.00
9215/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 G580 THI9.001,800.0016,200.00
10315/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 R187 THI10.001,800.0018,000.00
11SUM50,400.00
12116/09/2023CCF-1001BSTR_23452PAIDBS 1200R20 G580 JAP4.002,000.007,200.00
13SUM7,200.00
14117/09/2023CCF-1001BSTR_23453OUTSANDINGBS 1200R20 G580 JAP3.001,990.005,970.00
15217/09/2023CCF-1001BSTR_23453OUTSANDINGBS 750R16 R230 JAP2.00490.00980.00
16317/09/2023CCF-1001BSTR_23453OUTSANDINGBS 700R16 R230 JAP2.00440.00880.00
17417/09/2023CCF-1001BSTR_23453OUTSANDINGBS 1200R20 R187 THI3.001,770.005,310.00
18SUM13,140.00
19116/09/2023CCF-1001BSTR_23454PAIDBS 205/70R15C R623 JAP4.00520.002,080.00
20SUM2,080.00
SV





AFTER (2).xlsm
ABCDE
1ITEMBRANDQTYUNIT PRICEBALANCE
21GO 1200R20 AZ0026 CHI109809,800.00
32GO 1200R20 AZ0083 CHI2001010202,000.00
43BS 1200R20 G580 JAP10190019,000.00
54BS 1200R20 G580 THI20180036,000.00
65BS 1200R20 R187 THI10179017,900.00
76BS 1400R20 VSJ JAP10320032,000.00
87BS 1200R24 G580 JAP10220022,000.00
98BS 700R16 R230 JAP20043086,000.00
109BS 750R16 R230 JAP10049049,000.00
STOCK
Cell Formulas
RangeFormula
E2:E10E2=C2*D2



the result should be in REPORT sheet


AFTER (2).xlsm
ABC
1ITEMBRANDPRICE AVERAGE
21BS 700R16 R230 JAP423.33
32BS 750R16 R230 JAP493.33
43GO 1200R20 AZ0026 CHI920.00
54GO 1200R20 AZ0083 CHI1,000.00
65BS 1200R20 G580 JAP1,922.50
76BS 1200R20 G580 THI1,800.00
87BS 1200R20 R187 THI1,786.67
98BS 1200R24 G580 JAP2,200.00
109BS 1400R20 VSJ JAP3,200.00
1110BS 205/70R15C R623 JAP520.00
REPORT



so every new brand in two sheets will be show , the data in both sheets could be 10000 rows
 
@DanteAmor
when move the mouse pointer to the word "ky" will show different details with comparison for your picture !
w1.JPG



w2.JPG



and when use new version will show this
AFTER (2).xlsm
ABC
1ITEMBRANDPRICE AVERAGE
2115/06/2023
3215/09/2023
4316/09/2023
5417/09/2023
6
7
8
9
10
11
REPORT
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
1738536521317.png


Check the information on your sheets carefully.
According to your example, you have on sheet "SV", the BRAND in column "B". And on the "STOCK" sheet, the BRAND is in the "F" column.

If you changed the columns, no macros will work.

Show here the examples my macro is testing with.
:unsure:
 
Upvote 0
If you changed the columns, no macros will work.
I don't change anything !
Show here the examples my macro is testing with.
here is original data what I use it
merpri.xlsm
ABCDEFGHI
1ITEMDATECUSTOMERSINV.NOCASEBRANDQTYPRICEBALANCE
2115/06/2023CCF-1000BSTR_23448OUTSANDINGBS 750R16 R230 JAP3.00500.001,500.00
3215/06/2023CCF-1000BSTR_23448OUTSANDINGBS 700R16 R230 JAP2.00400.00800.00
4SUM2,300.00
5115/09/2023CCF-1000BSTR_23449OUTSANDINGGO 1200R20 AZ0026 CHI1.00920.00920.00
6215/09/2023CCF-1000BSTR_23449OUTSANDINGGO 1200R20 AZ0083 CHI1.001,000.001,000.00
7SUM1,920.00
8115/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 G580 JAP9.001,800.0016,200.00
9215/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 G580 THI9.001,800.0016,200.00
10315/09/2023CCF-1000BSTR_23450PAIDBS 1200R20 R187 THI10.001,800.0018,000.00
11SUM50,400.00
12116/09/2023CCF-1001BSTR_23452PAIDBS 1200R20 G580 JAP4.002,000.007,200.00
13SUM7,200.00
14117/09/2023CCF-1001BSTR_23453OUTSANDINGBS 1200R20 G580 JAP3.001,990.005,970.00
15217/09/2023CCF-1001BSTR_23453OUTSANDINGBS 750R16 R230 JAP2.00490.00980.00
16317/09/2023CCF-1001BSTR_23453OUTSANDINGBS 700R16 R230 JAP2.00440.00880.00
17417/09/2023CCF-1001BSTR_23453OUTSANDINGBS 1200R20 R187 THI3.001,770.005,310.00
18SUM13,140.00
19116/09/2023CCF-1001BSTR_23454PAIDBS 205/70R15C R623 JAP4.00520.002,080.00
20SUM2,080.00
SV





merpri.xlsm
ABCDE
1ITEMBRANDQTYUNIT PRICEBALANCE
21GO 1200R20 AZ0026 CHI10.00980.009,800.00
32GO 1200R20 AZ0083 CHI200.001,010.00202,000.00
43BS 1200R20 G580 JAP10.001,900.0019,000.00
54BS 1200R20 G580 THI20.001,800.0036,000.00
65BS 1200R20 R187 THI10.001,790.0017,900.00
76BS 1400R20 VSJ JAP10.003,200.0032,000.00
87BS 1200R24 G580 JAP10.002,200.0022,000.00
98BS 700R16 R230 JAP200.00430.0086,000.00
109BS 750R16 R230 JAP100.00490.0049,000.00
STOCK
Cell Formulas
RangeFormula
E2:E10E2=C2*D2
 
Upvote 0
here is original data what I use it
It's my fault. I reversed the sheets 😩


Here is the corrected macro.

VBA Code:
Sub Merge_price_as_average()
  Dim dic As Object
  Dim a As Variant, b As Variant, c As Variant, ky As Variant
  Dim i As Long, k As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("SV").Range("A2", Sheets("SV").Range("I" & Rows.Count).End(3)).Value
  b = Sheets("STOCK").Range("A2", Sheets("STOCK").Range("E" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1) + UBound(a, 2), 1 To 3)
  
  For i = 1 To UBound(a, 1)   'SV
    If a(i, 6) <> "" Then _
      If Not dic.exists(a(i, 6)) Then dic(a(i, 6)) = a(i, 8) & "|" & 1 Else _
        dic(a(i, 6)) = a(i, 8) + Split(dic(a(i, 6)), "|")(0) & "|" & Split(dic(a(i, 6)), "|")(1) + 1
  Next
  For i = 1 To UBound(b, 1)   'STOCK
    If b(i, 2) <> "" Then _
      If Not dic.exists(b(i, 2)) Then dic(b(i, 2)) = b(i, 4) & "|" & 1 Else _
        dic(b(i, 2)) = b(i, 4) + Split(dic(b(i, 2)), "|")(0) & "|" & Split(dic(b(i, 2)), "|")(1) + 1
  Next
  
  For Each ky In dic.keys
    k = k + 1
    c(k, 1) = k
    c(k, 2) = ky
    c(k, 3) = Split(dic(ky), "|")(0) / Split(dic(ky), "|")(1)
  Next
  
  With Sheets("REPORT")
    With .Range("A2:C" & Rows.Count)
      .ClearContents
      .HorizontalAlignment = xlCenter
    End With
    With .Range("A2").Resize(k, 3)
      .Value = c
      .Borders.LineStyle = xlContinuous
    End With
  End With
End Sub

😇
 
Upvote 0
Solution

Forum statistics

Threads
1,226,269
Messages
6,189,956
Members
453,584
Latest member
daihoctuxaeptit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top