Macro to compare old prices with new prices for stock and show(-/+)

[KalilMe]

Hello,
in QW sheet will be multiple columns contain prices and every time I will add new column contain new prices .
in STOCK sheet contains data in columns A:E
what I want create report in columns G:L with formatting and borders by brings the prices from last column in QW sheet and put in column J , as to column K=column I* column J
column L =column K- column E
in lastrow for column G when amount is minus in column L then populate LOSE word ,amount is plus in column L then populate PROFIT word.
if there is empty cell in last column in QW when brings price from previous column(not the first column) for instance if there is price in columnL,M,N and if the id doesn't existed price in column N then will brings from column M .
when create report will match BATCH column for two sheets , also if there is ID in STOCK sheet and it's not existed in QW sheet then will show as in STOCK sheet.

subtra.xlsm
	J	K	L	M
1	ITEM	BATCH	01/11/2024	02/11/2024
2	1	APP PP FRUIT	12.00	11.00
3	2	BAN FF FRUIT	9.00
4	3	CU CMC VEG	11.00	11.00
5	4	FRU BANN MU	22.00	23.00
6	5	PI PIA FRUIT	21.00	21.00
7	6	PO PTT VEG	12.00	11.00
8	7	TO TMA VEG	5.00	4.00
9	8	TE TEE FOOD	23.00	18.00
10	9	TRB POT BNG	22.00	24.00
QW

subtra.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM	BATCH	QTY	UNIT PRICE	TOTAL
2	1	TO TMA VEG	10.00	5.00	50.00
3	2	PO PTT VEG	20.00	10.00	200.00
4	3	BAN FF FRUIT	100.00	12.00	1,200.00
5	4	APP PP FRUIT	100.00	10.00	1,000.00
6	5	PI PIA FRUIT	12.00	22.00	264.00
7	6	TE TEE FOOD	100.00	23.00	2,300.00
8	7	CU CMC VEG	120.00	21.00	2,520.00
9	8	TRB POT BNG	100.00	22.00	2,200.00
10	9	FRU BANN MU	124.00	22.00	2,728.00
11
12
13
14
STOCK

result

subtra.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM	BATCH	QTY	UNIT PRICE	TOTAL		ITEM	BATCH	QTY	UNIT PRICE	TOTAL	D/P
2	1	TO TMA VEG	10.00	5.00	50.00		1	TO TMA VEG	10.00	4.00	40.00	-10.00
3	2	PO PTT VEG	20.00	10.00	200.00		2	PO PTT VEG	20.00	11.00	220.00	20.00
4	3	BAN FF FRUIT	100.00	12.00	1,200.00		3	BAN FF FRUIT	100.00	9.00	900.00	-300.00
5	4	APP PP FRUIT	100.00	10.00	1,000.00		4	APP PP FRUIT	100.00	11.00	1,100.00	100.00
6	5	PI PIA FRUIT	12.00	22.00	264.00		5	PI PIA FRUIT	12.00	21.00	252.00	-12.00
7	6	TE TEE FOOD	100.00	23.00	2,300.00		6	TE TEE FOOD	100.00	18.00	1,800.00	-500.00
8	7	CU CMC VEG	120.00	21.00	2,520.00		7	CU CMC VEG	120.00	11.00	1,320.00	-1,200.00
9	8	TRB POT BNG	100.00	22.00	2,200.00		8	TRB POT BNG	100.00	24.00	2,400.00	200.00
10	9	FRU BANN MU	124.00	22.00	2,728.00		9	FRU BANN MU	124.00	23.00	2,852.00	124.00
11							LOSE					-1,578.00
12
13
STOCK

Cell Formulas
Range	Formula
L2:L10	L2	=K2-E2
L11	L11	=SUM(L2:L10)

I need macro , I don't PQ or PT , formulas
thanks

 

[Peter_SSs]

this is not existed in QW sheet

I'm not sure what that means but if that is the QW sheet, what happened in your "right" results to the requirement to average?

 

[KalilMe]

I'm not sure what that means but if that is the QW sheet, what happened in your "right" results to the requirement to average?

as you did will show the same price without calculate average.

 

[kvsrinivasamurthy]

Sorry I did not observe the Columns in QW sheet. I mistook it as Columns A,B,C & D. Now I have changed to J,K,L, and M.

Sub CreateTable()
Application.ScreenUpdating = False
Dim Lrq&, Lrs&, Lcq&, T&, Ta&
Dim Frng1 As Range, Frng2 As Range

Lrq = Sheets("QW").Range("J" & Rows.Count).End(xlUp).Row
Lcq = Sheets("QW").Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets("STOCK")
Lrs = .Range("A" & Rows.Count).End(xlUp).Row
.Range("G1").CurrentRegion.Clear
.Range("A1").CurrentRegion.Resize(, 3).Copy .Range("G1")
For T = 2 To Lrs
Set Frng1 = Sheets("QW").Range("K2:K" & Lrq).Find(.Range("H" & T).Value)
If Not Frng1 Is Nothing Then
    For Ta = Lcq To Range("L1").Column Step -1
    If Sheets("QW").Cells(Frng1.Row, Ta) <> "" Then
    .Range("J" & T) = Sheets("QW").Cells(Frng1.Row, Ta)
    Exit For
    End If
    Next Ta
End If
Next T
.Range("K2:K" & Lrs).Formula = "=I2*J2"
.Range("L2:L" & Lrs).Formula = "=K2-E2"
.Range("G1:L1").Copy .Range("J1")
.Range("J1:L1") = Array("UNIT PRICE", "TOTAL", "D/P")
.Range("L" & Lrs + 1).Formula = "=Sum(L2:L" & Lrs & ")"
If .Range("L" & Lrs + 1) < 0 Then .Range("G" & Lrs + 1) = "LOSS" Else .Range("G" & Lrs + 1) = "GAIN"
.Range("H1").CurrentRegion.Borders.LineStyle = xlContinuous
End With

Application.ScreenUpdating = True
End Sub

 

[KalilMe]

@Fuji
based on post#30 there is missed value in column L.

sub.xlsm
	G	H	I	J	K	L
1	ITEM	BATCH	QTY	UNIT PRICE	TOTAL	D/P
2	1	TO TMA VEG	1000	33.00	33,000.00	32,950.00
3	2	PO PTT VEG	20	11.00	220.00	20.00
4	3	BAN FF FRUIT	100	11.00	1,100.00	-100.00
5	4	APP PP FRUIT	100	11.00	1,100.00	100.00
6	5	PI PIA FRUIT	12	21.00	252.00	-12.00
7	6	TE TEE FOOD	100	18.00	1,800.00	-500.00
8	7	CU CMC VEG	120	11.00	1,320.00	-1,200.00
9	8	TRB POT BNG	100	24.00	2,400.00	200.00
10	9	FRU BANN MU	124	45.00	5,580.00	2,852.00
11	10	FRU we3 MU	124	22.00	2,728.00
12	GAIN					34,310.00
STOCK

Cell Formulas
Range	Formula
L12	L12	=SUM(L$2:L11)

 

[KalilMe]

@kvsrinivasamurthy
there are missed values for new item in STOCK sheet for row 11.
the same problem ,check post#27.

sub.xlsm
	G	H	I	J	K	L
1	ITEM	BATCH	QTY	UNIT PRICE	TOTAL	D/P
2	1	TO TMA VEG	1,000.00	33	33000	32,950.00
3	2	PO PTT VEG	20.00	11	220	20.00
4	3	BAN FF FRUIT	100.00	11	1100	-100.00
5	4	APP PP FRUIT	100.00	11	1100	100.00
6	5	PI PIA FRUIT	12.00	21	252	-12.00
7	6	TE TEE FOOD	100.00	18	1800	-500.00
8	7	CU CMC VEG	120.00	11	1320	-1,200.00
9	8	TRB POT BNG	100.00	24	2400	200.00
10	9	FRU BANN MU	124.00	45	5580	2,852.00
11	10	FRU we3 MU	124.00		0	-2,728.00
12	GAIN					31,582.00
STOCK

Cell Formulas
Range	Formula
K2:K11	K2	=I2*J2
L2:L11	L2	=K2-E2
L12	L12	=SUM(L2:L11)

 

[Fuji]

What row of col.L?

If we are talking about difference data set, it is just wasting time.

Upload a workbook with the exact data and exact desire result that you want.

 

[kvsrinivasamurthy]

FRU we3 MU

is not available in QW sheet.

 

[KalilMe]

If we are talking about difference data set, it is just wasting time.

yes you're right ,sorry .
ok it just remaining this part

just I would create borders, I would a slight modification if you don't mind, please.
I would show average price instead of brings price from last column in QW sheet.
example for ID "TE TEE FOOD" QW sheet=18 and the price in stock sheet=23 so when show in result will be average(18,23)=21

I posted in post#4 I forgot mentioning in OP ,sorry about it
it's up to you if you still interest .
thank you for your time.

 

[KalilMe]

is not available in QW sheet.

that's correct
if it's not in QW sheet should also show as in STOCK sheet , that's why I ask from you check post#27.

 

[Fuji]

First of all, is my code working for your initial requirement or not?

If not, you should tell me how is it not working.
If it is working and change the UNIT PRICE to average, how do you want to calculate the TOTAL?

Better upload your workbook with your EXACT desired results to avoid any confusion.