Macro to populate loss when compare sales price with cost price

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
682
Office Version
  1. 2019
Hi,
in SR sheet I have invoices contains brands is relating with sales price and in REPORT sheet contains brands is relating with cost price . so what I want match brands in column F and price in column H for SR sheet with REPORT sheet for brands in column B and average price in column C then will create report in columns F:J will brings dates and brands and QTY and price from SR sheet (price should be smaller than price in REPORT sheet) and in BALANCE column =(I-C)*H and insert sum row to sum BALANCE column .
as to new items in SR sheet will not show in report, also if there is new brand in REPORT sheet will not show.
so just show brands are existed in both sheets based on smaller price in SR sheet than price in REPORT sheet.
every time should delete data in F:J before brings data .
the data could be 10000 rows for SR sheet and 5000 rows for range A:C in REPORT sheet.
(2).xlsm
ABCDEFGHI
1ITEMDATECUSTOMERSINV.NOCASEBRANDQTYPRICEBALANCE
2115/06/2023CCF-1000BSJ_23444OUTSANDINGBS 750R16 R230 JAP4.00510.002,040.00
3215/06/2023CCF-1000BSJ_23444OUTSANDINGBS 1200R20 G580 JAP4.002,000.008,000.00
4SUM10,040.00
5116/06/2023CCF-1001BSJ_23445OUTSANDINGBS 750R16 R230 JAP2.00470.00940.00
6SUM940.00
7116/06/2023CCF-1002BSJ_23446PAIDGO 1200R20 AZ0026 CHI2.00850.001,700.00
8216/06/2023CCF-1002BSJ_23446PAIDBS 1200R20 G580 JAP2.001,900.003,800.00
9SUM5,500.00
10117/09/2023CCF-1003BSJ_23447PAIDBS 1200R20 G580 JAP1.001,750.001,750.00
11217/09/2023CCF-1003BSJ_23447PAIDBS 700R16 R230 JAP2.00410.00820.00
12SUM2,570.00
13118/09/2023CCF-1004BSJ_23448PAIDBS 1200R20 G580 JAP1.001,990.001,990.00
14218/09/2023CCF-1004BSJ_23448PAIDBS 700R16 R230 JAP2.00540.001,080.00
15SUM3,070.00
16118/09/2023CCF-1005BSJ_23449PAIDBS 215/70R15C R623 THI1.00440.00440.00
17218/09/2023CCF-1005BSJ_23449PAIDBS225/70R15C R623 THI1.00650.00650.00
18SUM1,090.00
SR




(2).xlsm
ABC
1ITEMBRANDPRICE AVERAGE
21BS 700R16 R230 JAP423.33
32BS 750R16 R230 JAP493.33
43GO 1200R20 AZ0026 CHI920.00
54GO 1200R20 AZ0083 CHI1,005.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

AFTER (2).xlsm
ABCDEFGHIJKL
1ITEMBRANDPRICE AVERAGE
21BS 700R16 R230 JAP423.33
32BS 750R16 R230 JAP493.33
43GO 1200R20 AZ0026 CHI920.00
54GO 1200R20 AZ0083 CHI1,005.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




result should be
(2).xlsm
ABCDEFGHIJKL
1ITEMBRANDPRICE AVERAGEDATEBRANDQTYPRICEBALANCE
21BS 700R16 R230 JAP423.3315/09/2023BS 700R16 R230 JAP2.00410.00-26.66
32BS 750R16 R230 JAP493.3316/09/2023BS 750R16 R230 JAP2.00470.00-46.67
43GO 1200R20 AZ0026 CHI920.0016/09/2023BS 1200R20 G580 JAP2.001,900.00-45.00
54GO 1200R20 AZ0083 CHI1,005.0017/09/2023BS 1200R20 G580 JAP1.001,750.00-172.50
65BS 1200R20 G580 JAP1,922.50SUM-290.83
76BS 1200R20 G580 THI1,800.00
87BS 1200R20 R187 THI1,786.67
98BS 1200R24 G580 JAP2,200.00
REPORT
Cell Formulas
RangeFormula
J2:J3J2=(I2-C2)*H2
J4J4=(I4-C6)*H4
J5J5=(I5-C6)*H5
J6J6=SUM(J2:J5)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try:

VBA Code:
Sub compare_sales_price_cost_price()
  Dim a As Variant, b As Variant
  Dim shR As Worksheet, shS As Worksheet
  Dim dic As Object
  Dim i As Long, k As Long, lr As Long
  Dim tot As Double
 
  Set shR = Sheets("REPORT")
  Set shS = Sheets("SR")
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = shR.Range("A2", shR.Range("C" & Rows.Count).End(3)).Value
  b = shS.Range("A2", shS.Range("I" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(b, 1), 1 To 5)
 
  For i = 1 To UBound(a, 1)
    dic(a(i, 2)) = a(i, 3)
  Next
 
  For i = 1 To UBound(b, 1)
    If dic.exists(b(i, 6)) Then
      If b(i, 8) < dic(b(i, 6)) Then
        k = k + 1
        c(k, 1) = b(i, 2)                             'date
        c(k, 2) = b(i, 6)                             'brand
        c(k, 3) = b(i, 7)                             'qty
        c(k, 4) = b(i, 8)                             'price
        c(k, 5) = (b(i, 8) - dic(b(i, 6))) * b(i, 7)  'balance
        tot = tot + c(k, 5)
      End If
    End If
  Next
 
  Application.ScreenUpdating = False
  shR.Range("F2:J" & Rows.Count).Clear
  shR.Range("F2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  lr = shR.Range("F" & Rows.Count).End(3).Row + 1
  shR.Range("F" & lr).Value = "SUM"
  shR.Range("F" & lr).Interior.Color = 8696052
  shR.Range("J" & lr).Value = tot
  shR.Range("F2:J" & lr).Borders.LineStyle = xlContinuous
  shR.Range("F:J").HorizontalAlignment = xlCenter
  shR.Range("F:F").NumberFormat = "dd/mm/yyyy"
  shR.Range("H:J").NumberFormat = "#,##0.00"
  Application.ScreenUpdating = True
End Sub

In your example the dates are not correct and you were missing a Brand, the result should be:
DANTE AMOR
FGHIJ
1DATEBRANDQTYPRICEBALANCE
216/06/2023BS 750R16 R230 JAP2.00470.00-46.67
316/06/2023GO 1200R20 AZ0026 CHI2.00850.00-140.00
416/06/2023BS 1200R20 G580 JAP2.001,900.00-45.00
517/09/2023BS 1200R20 G580 JAP1.001,750.00-172.50
617/09/2023BS 700R16 R230 JAP2.00410.00-26.66
7SUM-430.83
REPORT


😇
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,264
Messages
6,189,928
Members
453,582
Latest member
Browny2821

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