Help

[Phill032]

Hi All, any help on this would be greatly appreciated.
The data below refers to a Used car stocklist advertising changes. Each line represents a log of a price change on the internet.
I need the following data retrieved from this list.
1 - Total number of internet changes for each car
2 - The Date of the last previous change
3 - The amount of the last previous change
4 - Total Price change variance

Stock#	Value Before	Value After	Log Date
1234	0	13,888	01/03/2019
1234	13,888	13,945	03/03/2019
1234	13,945	13,940	06/03/2019
1235	0	15,990	04/03/2019
1235	15,990	16,580	06/03/2019
1235	16,580	16,690	07/03/2019

<tbody>
</tbody>

 

[RasGhul]

Hi Phil,

Can you use this?

Book1
	A	B	C	D	E	F	G
2	Stock#	Value Before	Value After	Log Date
3	1234	0	13,888	01-03-19
4	1234	13,888	13,945	03-03-19
5	1234	13,945	13,940	06-03-19
6	1235	0	15,990	04-03-19
7	1235	15,990	16,580	06-03-19
8	1235	16,580	16,690	07-03-19
9
10	Stock	Internet Changes #	Date of Last Change	Amount of Last	Min	Max	%
11	1234	3	06-03-19	13,940	13,888	13,945	0.4%
12	1235	3	07-03-19	16,690	15,990	16,690	4.2%
Sheet1

Cell Formulas
Range	Formula
A11	=INDEX($A$3:$A$8,MATCH(0,INDEX(COUNTIF($A$10:A10,$A$3:$A$8),),0))
B11	=COUNTIFS($A$3:$A$8,A11,$D$3:$D$8,">0")
D11	=SUMPRODUCT(($A$3:$A$8=A11)*($D$3:$D$8=C11)*$C$3:$C$8)
G11	=(F11-E11)/F11
C11	{=MAX(IF($A$3:$A$8=A11,$D$3:$D$8))}
E11	{=MIN(IF($A$3:$A$8=A11,$C$3:$C$8))}
F11	{=MAX(IF($A$3:$A$8=A11,$C$3:$C$8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phill032]

Thanks, Almost works perfectly.. only the previous price change amount is not the last amount is is the second last amount.
So for stock# 1234 the value would be 13,945. other than that it seems to work perfectly

 

[sandy666]

like this?

with PowerQuery (Get&Transform)

Stock#	Value Before	Value After	Log Date		Stock#	Changes	LastDate	LastAmount	Min	Max	%
1234​	0​	13,888​	01/03/2019​		1234​	3​	06/03/2019​	13,945​	13,888​	13,945​	0.41%​
1234​	13,888​	13,945​	03/03/2019​		1235​	3​	07/03/2019​	16,690​	15,990​	16,690​	4.19%​
1234​	13,945​	13,940​	06/03/2019​
1235​	0​	15,990​	04/03/2019​
1235​	15,990​	16,580​	06/03/2019​
1235​	16,580​	16,690​	07/03/2019​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    Sort = Table.Sort(Source,{{"Value After", Order.Ascending}}),
    Date = Table.TransformColumnTypes(Sort,{{"Stock#", Int64.Type}, {"Value Before", Int64.Type}, {"Value After", Int64.Type}, {"Log Date", type date}}),
    Group = Table.Group(Date, {"Stock#"}, {{"Changes", each Table.RowCount(_), type number}, {"LastDate", each List.Max([Log Date]), type date}, {"LastAmount", each List.Max([Value After]), type number}, {"Min", each List.Min([Value After]), type number}, {"Max", each List.Max([Value After]), type number}}),
    Percent = Table.AddColumn(Group, "%", each ([Max]-[Min])/[Max])
in
    Percent[/SIZE]

 

[Phill032]

Yep, that is about it.. unsure of your code though?

 

[sandy666]

but imho it should be like in post#2

Stock#	Changes	LastDate	LastAmount	Min	Max	%
1234​	3​	06/03/2019​	13940​	13,888​	13,945​	0.41%​
1235​	3​	07/03/2019​	16690​	15,990​	16,690​	4.19%​

 

[sandy666]

what is your excel version?

 

[Phill032]

ok so maybe im just not catching on here.. The code you have attached looks to me like VBA, post 2 i just typed up as normal formulas.
Can i just past your code? The data i supplied is only a small extract of the data in my worksheet.

 

[sandy666]

M-code (PowerQuery) has nothing to do with vba

that's why I asked about your excel version...

Excel 2010/2013 free PowerQuery add-in
Excel 2016 and higher PowerQuery (Get&Transform) is built-in

edit:
I need to go now, will be later

 

[Phill032]

2007, sorry missed that post.