Help

P

Phill032

Board Regular
Joined
Nov 9, 2016
Messages
51
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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Stock#[/TD]
[TD]Value Before[/TD]
[TD]Value After[/TD]
[TD]Log Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]0[/TD]
[TD]13,888[/TD]
[TD]01/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]13,888[/TD]
[TD]13,945[/TD]
[TD]03/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]13,945[/TD]
[TD]13,940[/TD]
[TD]06/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]0[/TD]
[TD]15,990[/TD]
[TD]04/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]15,990[/TD]
[TD]16,580[/TD]
[TD]06/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]16,580[/TD]
[TD]16,690[/TD]
[TD]07/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi Phil,

Can you use this?


Book1
ABCDEFG
2Stock#Value BeforeValue AfterLog Date
31234013,88801-03-19
4123413,88813,94503-03-19
5123413,94513,94006-03-19
61235015,99004-03-19
7123515,99016,58006-03-19
8123516,58016,69007-03-19
9
10StockInternet Changes #Date of Last ChangeAmount of LastMinMax%
111234306-03-1913,94013,88813,9450.4%
121235307-03-1916,69015,99016,6904.2%
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
like this?

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Stock#[/td][td=bgcolor:#5B9BD5]Value Before[/td][td=bgcolor:#5B9BD5]Value After[/td][td=bgcolor:#5B9BD5]Log Date[/td][td][/td][td=bgcolor:#70AD47]Stock#[/td][td=bgcolor:#70AD47]Changes[/td][td=bgcolor:#70AD47]LastDate[/td][td=bgcolor:#70AD47] LastAmount [/td][td=bgcolor:#70AD47] Min [/td][td=bgcolor:#70AD47] Max [/td][td=bgcolor:#70AD47]%[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1234​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
13,888​
[/td][td=bgcolor:#DDEBF7]
01/03/2019​
[/td][td][/td][td=bgcolor:#E2EFDA]
1234​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
06/03/2019​
[/td][td=bgcolor:#E2EFDA]
13,945​
[/td][td=bgcolor:#E2EFDA]
13,888​
[/td][td=bgcolor:#E2EFDA]
13,945​
[/td][td=bgcolor:#E2EFDA]
0.41%​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1234​
[/td][td]
13,888​
[/td][td]
13,945​
[/td][td]
03/03/2019​
[/td][td][/td][td]
1235​
[/td][td]
3​
[/td][td]
07/03/2019​
[/td][td]
16,690​
[/td][td]
15,990​
[/td][td]
16,690​
[/td][td]
4.19%​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1234​
[/td][td=bgcolor:#DDEBF7]
13,945​
[/td][td=bgcolor:#DDEBF7]
13,940​
[/td][td=bgcolor:#DDEBF7]
06/03/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1235​
[/td][td]
0​
[/td][td]
15,990​
[/td][td]
04/03/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1235​
[/td][td=bgcolor:#DDEBF7]
15,990​
[/td][td=bgcolor:#DDEBF7]
16,580​
[/td][td=bgcolor:#DDEBF7]
06/03/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1235​
[/td][td]
16,580​
[/td][td]
16,690​
[/td][td]
07/03/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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]
 
Last edited:
Upvote 0
but imho it should be like in post#2

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Stock#[/td][td=bgcolor:#70AD47]Changes[/td][td=bgcolor:#70AD47]LastDate[/td][td=bgcolor:#70AD47] LastAmount [/td][td=bgcolor:#70AD47] Min [/td][td=bgcolor:#70AD47] Max [/td][td=bgcolor:#70AD47]%[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1234​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
06/03/2019​
[/td][td=bgcolor:#E2EFDA]
13940​
[/td][td=bgcolor:#E2EFDA]
13,888​
[/td][td=bgcolor:#E2EFDA]
13,945​
[/td][td=bgcolor:#E2EFDA]
0.41%​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1235​
[/td][td]
3​
[/td][td]
07/03/2019​
[/td][td]
16690​
[/td][td]
15,990​
[/td][td]
16,690​
[/td][td]
4.19%​
[/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Find last value in row that is not a percentage value
2 3
Replies
24
Views
2K
Peter_SSs
Peter_SSs
D
  • Question Question
Tracking Project Grand Total Impact
Replies
1
Views
421
pbornemeier
pbornemeier
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
P
  • Question Question
retrieving the last date in a list
Replies
2
Views
499
Phill032
P
M
  • Question Question
Find and delete rows that contain date's that change.
Replies
1
Views
661
MickG
M

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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