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

My comment in post #28 is about the fact that in your "right" results in post #27 you don't seem to have averaged any of the prices (or at least not most of them)

You have marked post #13 as the correct solution. Is that still the case?

 

[KalilMe]

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

yes.

If it is working and change the UNIT PRICE to average, how do you want to calculate the TOTAL?

as in original requirement should QTY*UNIT PRICE.

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

Dropbox

 

[KalilMe]

My comment in post #28 is about the fact that in your "right" results in post #27 you don't seem to have averaged any of the prices (or at least not most of them)

I know , but I would fix the problem based on requirements OP first of all and I will ask about average prices later.
that was in yesterday but suddenly today doesn't work well for new ID in STOCK sheet , I no know why !
see the row11 how show value in last COL(L). . it supposes zero.
you can check the attaching in post#42.

sub.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
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.50	45.00	-5.00
3	2	PO PTT VEG	20.00	10.00	200.00		2	PO PTT VEG	20.00	10.50	210.00	10.00
4	3	BAN FF FRUIT	100.00	12.00	1,200.00		3	BAN FF FRUIT	100.00	10.50	1,050.00	-150.00
5	4	APP PP FRUIT	100.00	10.00	1,000.00		4	APP PP FRUIT	100.00	10.50	1,050.00	50.00
6	5	PI PIA FRUIT	12.00	22.00	264.00		5	PI PIA FRUIT	12.00	21.50	258.00	-6.00
7	6	TE TEE FOOD	100.00	23.00	2,300.00		6	TE TEE FOOD	100.00	20.50	2,050.00	-250.00
8	7	CU CMC VEG	120.00	21.00	2,520.00		7	CU CMC VEG	120.00	16.00	1,920.00	-600.00
9	8	TRB POT BNG	100.00	22.00	2,200.00		8	TRB POT BNG	100.00	23.00	2,300.00	100.00
10	9	FRU BANN MU	124.00	22.00	2,728.00		9	FRU BANN MU	124.00	22.50	2,790.00	62.00
11	10	FRU TT MU	124.00	18.00	2,728.00		10	FRU TT MU	124.00	18.00	2,232.00	-496.00
12							LOSE					-1,285.00
13
STOCK

 

[kvsrinivasamurthy]

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.

What value expect in J11

 

[KalilMe]

What value expect in J11

not only J11 also K11 should brings unit price from column D and in K11 =QTY*UNIT price and in L11 will be zero

 

[kvsrinivasamurthy]

TRy.

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
Else
.Range("I" & T) = .Range("C" & T)
.Range("J" & T) = .Range("D" & T)
End If
Next T
.Range("K2:K" & Lrs).Fo rmula = "=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

 

[Peter_SSs]

see the row11 how show value in last COL(L). . it supposes zero.

It is not zero because, by my reckoning, the data in columns A:E is incorrect.
C11 = 124.00
D11 = 18.00
C11 * D11 = 124.00 * 18.00 = 2,232
So why does your E11 show 2,728? That doesn't make sense to me.

How did the values in column E of 'STOCK' get there?
I all the other rows besides row 11, the Column E value could be obtained by multiplying the Qty * Unit Price. Why is row 11 different?

KalilMe_sub.xlsm
	C	D	E	F
1	QTY	UNIT PRICE	TOTAL
2	10.00	5.00	50.00	50.00
3	20.00	10.00	200.00	200.00
4	100.00	12.00	1,200.00	1,200.00
5	100.00	10.00	1,000.00	1,000.00
6	12.00	22.00	264.00	264.00
7	100.00	23.00	2,300.00	2,300.00
8	120.00	21.00	2,520.00	2,520.00
9	100.00	22.00	2,200.00	2,200.00
10	124.00	22.00	2,728.00	2,728.00
11	124.00	18.00	2,728.00	2,232.00
STOCK

Cell Formulas
Range	Formula
F2:F11	F2	=C2*D2

 

[kvsrinivasamurthy]

I have modified code. Pl try.
.

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
Else
.Range("J" & T) = .Range("D" & T)
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]

So why does your E11 show 2,728? That doesn't make sense to me.

OMG!
this is really bad, sorry!
this happens when I test new ID by pull down without calculation.
so your suggestion still solve my requirements.
look you see I still post after you solve this thread. I want to you know after see kvsrinivasamurthy's , Fuji comments just guys try to share their solution and I inform them to know this is what I want or not.
moreover variety solutions could be useful with different way, that's it.

 

[Fuji]

Where is your desired result with the average unit cost?