compare brands between two sheets and show minus and positive values based on date (today)

[abdo meghari]

Hello

I want comparing BRAND column between two sheets .

the result will be in ACTUAL sheet in H:J columns based on DATE (TODAY)

so in ACTUAL sheet should merge duplicates BRANDS after that will subtract merged QTY from QTY for the same BRAND in STOCK sheet .

if there is brand =0 between two sheets when subtract from each other no need show . just if show plus or deficit values.
if there are no brands in ACTUAL sheet and there are in STOCK sheet then should show minus values in ACTUAL sheet.

should show based on date(today)
note: when date(today) is 29/08/2024 then should keep data were date(today) "28/08/2024"
rather than don't delete old dates when populate data for date (today).

TGR.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	BS 1200R20 G580 JAP	200.00
3	2	BS 1200R20 G580 THI	150.00
4	3	BS 205/70R15C R623 THI	120.00
5	4	DT 1200R20 DS 50 THI	100.00
6	5	BS 750R16 8PR R230 JAP	200.00
7	6	BS 750R16 10PR VSJ JAP	100.00
8	7	BSJ 1400R20 VSJ JAP	120.00
9	8	BSJ 1400R20 R180 JAP	700.00
10	9	BS 315/R22.5 R184 THI	500.00
11	10	BS 1200R24 G580 JAP	150.00
12
STOCK

TGR.xlsm
	B	C	D	E	F	G	H	I	J
1	DATE	INVOICE NO	CLIENT NO	BRAND	QTY
2	28/08/2024	BSJ1000	BBBFT-00	BS 1200R20 G580 JAP	150.00
3	28/08/2024	BSJ1001	BBBFT-00	BS 1200R20 G580 JAP	20.00
4	28/08/2024	BSJ1001	BBBFT-01	BS 1200R20 G580 THI	160.00
5	28/08/2024	BSJ1001	BBBFT-01	DT 1200R20 DS 50 THI	80.00
6	28/08/2024	BSJ1002	BBBFT-02	DT 1200R20 DS 50 THI	20.00
ACTUAL

result

TGR.xlsm
	B	C	D	E	F	G	H	I	J
1	DATE	INVOICE NO	CLIENT NO	BRAND	QTY		DATE	BRAND	QTY
2	28/08/2024	BSJ1000	BBBFT-00	BS 1200R20 G580 JAP	150.00		28/08/2024	BS 1200R20 G580 JAP	-30.00
3	28/08/2024	BSJ1001	BBBFT-00	BS 1200R20 G580 JAP	20.00		28/08/2024	BS 1200R20 G580 THI	10.00
4	28/08/2024	BSJ1001	BBBFT-01	BS 1200R20 G580 THI	160.00		28/08/2024	BS 205/70R15C R623 THI	-120.00
5	28/08/2024	BSJ1001	BBBFT-01	DT 1200R20 DS 50 THI	80.00		28/08/2024	BS 750R16 8PR R230 JAP	-200.00
6	28/08/2024	BSJ1002	BBBFT-02	DT 1200R20 DS 50 THI	20.00		28/08/2024	BS 750R16 10PR VSJ JAP	-100.00
7							28/08/2024	BSJ 1400R20 VSJ JAP	-120.00
8							28/08/2024	BSJ 1400R20 R180 JAP	-700.00
9							28/08/2024	BS 315/R22.5 R184 THI	-500.00
10							28/08/2024	BS 1200R24 G580 JAP	-150.00
ACTUAL

 

[DanteAmor]

Try:

Sub compare_brands()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim n As Double, i As Long, k As Long, lr As Long
  Dim a As Variant, b As Variant
  
  Set sh1 = Sheets("STOCK")
  Set sh2 = Sheets("ACTUAL")
  
  a = sh1.Range("B2", sh1.Range("C" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 3)
  
  lr = sh2.Range("D" & Rows.Count).End(3).Row
  For i = 1 To UBound(a, 1)
    n = WorksheetFunction.SumIfs(sh2.Range("E2:E" & lr), sh2.Range("A2:A" & lr), _
          Date, sh2.Range("D2:D" & lr), a(i, 1)) - a(i, 2)
    If n <> 0 Then
      k = k + 1
      b(k, 1) = Date
      b(k, 2) = a(i, 1)
      b(k, 3) = n
    End If
  Next
  
  sh2.Range("G" & Rows.Count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[abdo meghari]

Hi Dante ,again !
I tested , but shows error results ,for instance 1200R20 G580 JAP = -200 based on your code .
the right should merge duplicates first =150+20=170 after that should subtract like this 170-200=-30.

 

[DanteAmor]

The macro is correct, but you should try with today's dates:

If the records on the "ACTUAL" sheet are dated August 28, which is how you tested, then none will be considered.
Try records dated August 30 or the day you take the test

 

[abdo meghari]

OK
see the last highlighted by red what I got , but the right should be -50, not -150 as show.

TGR.xlsm
	B	C	D	E	F	G	H	I	J
1	DATE	INVOICE NO	CLIENT NO	BRAND	QTY		DATE	BRAND	QTY
2	28/08/2024	BSJ1000	BBBFT-00	BS 1200R20 G580 JAP	150.00		28/08/2024	BS 1200R20 G580 JAP	-30.00
3	28/08/2024	BSJ1001	BBBFT-00	BS 1200R20 G580 JAP	20.00		28/08/2024	BS 1200R20 G580 THI	10.00
4	28/08/2024	BSJ1001	BBBFT-01	BS 1200R20 G580 THI	160.00		28/08/2024	BS 205/70R15C R623 THI	-120.00
5	28/08/2024	BSJ1001	BBBFT-01	DT 1200R20 DS 50 THI	80.00		28/08/2024	BS 750R16 8PR R230 JAP	-200.00
6	28/08/2024	BSJ1002	BBBFT-02	DT 1200R20 DS 50 THI	20.00		28/08/2024	BS 750R16 10PR VSJ JAP	-100.00
7	30/08/2024	BSJ1003	BBBFT-03	BS 1200R24 G580 JAP	50.00		28/08/2024	BSJ 1400R20 VSJ JAP	-120.00
8	30/08/2024	BSJ1004	BBBFT-04	BS 1200R24 G580 JAP	50.00		28/08/2024	BSJ 1400R20 R180 JAP	-700.00
9							28/08/2024	BS 315/R22.5 R184 THI	-500.00
10							28/08/2024	BS 1200R24 G580 JAP	-150.00
11							30/08/2024	BS 1200R20 G580 JAP	-200.00
12							30/08/2024	BS 1200R20 G580 THI	-150.00
13							30/08/2024	BS 205/70R15C R623 THI	-120.00
14							30/08/2024	DT 1200R20 DS 50 THI	-100.00
15							30/08/2024	BS 750R16 8PR R230 JAP	-200.00
16							30/08/2024	BS 750R16 10PR VSJ JAP	-100.00
17							30/08/2024	BSJ 1400R20 VSJ JAP	-120.00
18							30/08/2024	BSJ 1400R20 R180 JAP	-700.00
19							30/08/2024	BS 315/R22.5 R184 THI	-500.00
20							30/08/2024	BS 1200R24 G580 JAP	-150.00
ACTUAL

 

[DanteAmor]

That's correct, for me the result is -50

In the Stock sheet you must have 150:

varios 29ago2024.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	BS 1200R20 G580 JAP	200
3	2	BS 1200R20 G580 THI	150
4	3	BS 205/70R15C R623 THI	120
5	4	DT 1200R20 DS 50 THI	100
6	5	BS 750R16 8PR R230 JAP	200
7	6	BS 750R16 10PR VSJ JAP	100
8	7	BSJ 1400R20 VSJ JAP	120
9	8	BSJ 1400R20 R180 JAP	700
10	9	BS 315/R22.5 R184 THI	500
11	10	BS 1200R24 G580 JAP	150
STOCK

 

[abdo meghari]

my bad !
sorry I waste your time.
I was rushing by re-copy my data in different range (not matched structure data as in OP)
every thing is ok and work as I want.
many thanks Dante.