Search for Date of Last Price Change

[andrewr1]

I was hoping that someone can help me in writing array formulas in excel that would return pricing from "Last Ordered" and the "Date of price changed" from a list of invoices.

Below would be the example of the information that I need from the data table. Please help.

Item#: 201 (entered value)
Last Ordered: 4/24/18 (formula, the latest invoice that includes the item#, this is where I need the help)
Current Price: $7.00 (formula, pricing from latest invoice, which can be a simple vlookup from "Last Order")
Date of Price Change : 8/28/17 (formula, the most recent invoice that is <> the Current Price, this is where I need the help)
Previous Price: $6.50 (formula, which can be a simple vlookup from "Date of Price Change")

In this example the "Date of Price Change" formula would skip over 11/20/17 because the pricing is still $7.00. The pricing is not <> the current price. And would instead return 8/28/17 because that price < current price.

Thanks again for any help


Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD] Column C
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Item #
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]5/11/18
[/TD]
[TD]324
[/TD]
[TD]$7.00
[/TD]
[/TR]
[TR]
[TD]4/24/18
[/TD]
[TD]201
[/TD]
[TD]$7.00
[/TD]
[/TR]
[TR]
[TD]1/1/18
[/TD]
[TD]324
[/TD]
[TD]$6.00
[/TD]
[/TR]
[TR]
[TD]11/20/17
[/TD]
[TD]201
[/TD]
[TD]$7.00
[/TD]
[/TR]
[TR]
[TD]10/9/17
[/TD]
[TD]324
[/TD]
[TD]$6.00
[/TD]
[/TR]
[TR]
[TD]10/9/17
[/TD]
[TD]324
[/TD]
[TD]$6.00
[/TD]
[/TR]
[TR]
[TD]8/28/17
[/TD]
[TD]201
[/TD]
[TD]$6.50
[/TD]
[/TR]
[TR]
[TD]7/17/17
[/TD]
[TD]201
[/TD]
[TD]$6.50
[/TD]
[/TR]
[TR]
[TD]6/5/17
[/TD]
[TD]324
[/TD]
[TD]$5.50
[/TD]
[/TR]
[TR]
[TD]4/24/17
[/TD]
[TD]201
[/TD]
[TD]$6.00
[/TD]
[/TR]
[TR]
[TD]3/13/17
[/TD]
[TD]324
[/TD]
[TD]$5.50
[/TD]
[/TR]
[TR]
[TD]1/30/17
[/TD]
[TD]201
[/TD]
[TD]6.50
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

	A	B	C	D	E	F	G
Last Ordered:
Current Price
Previous Price
Previous Date
8

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Item #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Price[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Item[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/11/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$7.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/24/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$7.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.00[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/24/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/24/2018[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$7.00[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$7.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/9/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.00[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/9/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.00[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/28/2017[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/28/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7/17/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/5/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/24/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$6.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3/13/2017[/TD]
[TD="align: right"]324[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=MAXIFS(A2:A13,B2:B13,E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]{=MAX(IF(B2:B13=E2,A2:A13))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]{=INDEX(C2:C13,MATCH(E2&"|"&F4,B2:B13&"|"&A2:A13,0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]{=IFERROR(INDEX(C2:C13,SMALL(IF(B2:B13=E2,IF(C2:C13<>F5,ROW(C2:C13)-ROW(C2)+1)),1)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]{=IF(F6="","",INDEX(A2:A13,MATCH(E2&"|"&F6,B2:B13&"|"&C2:C13,0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If you don't have the MAXIFS function (F4 formula), the G4 array formula should work.

 

[Peter_SSs]

Given the layout of your data, I think that you could also try these formulas, none of which require the Ctrl+Shift+Enter confirmation.
(Note that my dates are in d/m/y format)

Excel Workbook
	A	B	C	D	E	F
1	Date	Item #	Price		Item	201
2	11/05/2018	324	$7.00		Last Ordered	24/04/2018
3	24/04/2018	201	$7.00		Current Price	$7.00
4	1/01/2018	324	$6.00		Previous Price	$6.50
5	20/11/2017	201	$7.00		Previous Date	28/08/2017
6	9/10/2017	324	$6.00
7	9/10/2017	324	$6.00
8	28/08/2017	201	$6.50
9	17/07/2017	201	$6.50
10	5/06/2017	324	$5.50
11	24/04/2017	201	$6.00
12	13/03/2017	324	$5.50
13	30/01/2017	201	$6.50
Price Changes

 

[andrewr1]

Thanks Eric that worked great! Doesn't an array take up a significant of memory? If I need to repeat these formulas a significant number of times, is that going to give Excel a problem.

 

[andrewr1]

Thanks Pete, that also worked well! I like the see the different techniques.

I have not worked with the aggregate function in indexes. I am going to have to look into that one. Thanks again.

 

[Eric W]

Glad it works for you!

An array takes up as much memory as it needs to handle the range you give it. And if you give it a large range, it could take a lot of time too. The problem is that your question seems to require array processing. If your sheet starts to bog down, you could give Peter's formulas a shot. His F4:F5 formulas are still array formulas, even if they don't require CSE, but since they use different functions, they still could be faster. I couldn't say without some testing.

If those still cause issues, you could replace them with an event macro. Whenever you enter your item number in E2 or F1, or whatever cell(s) you want, the macro would kick off and look up the results. So it would only run as needed. Let us know how the formulas work first though.

 

[Peter_SSs]

Thanks Pete, that also worked well! I like the see the different techniques.

I have not worked with the aggregate function in indexes. I am going to have to look into that one. Thanks again.

Glad to contribute. I agree with Eric's comments about the array formulas. If you have a lot of them and the ranges being assessed are large, there could be a performance impact but let's just see how it goes.

 

[ANDREAC247]

Try:

	A	B	C	D	E	F	G
1	Date	Item #	Price		Item
2	5/11/2018	324	$7.00		201
3	4/24/2018	201	$7.00
4	1/1/2018	324	$6.00		Last Ordered:	4/24/2018	4/24/2018
5	11/20/2017	201	$7.00		Current Price	$7.00
6	10/9/2017	324	$6.00		Previous Price	$6.50
7	10/9/2017	324	$6.00		Previous Date	8/28/2017
8	8/28/2017	201	$6.50
9	7/17/2017	201	$6.50
10	6/5/2017	324	$5.50
11	4/24/2017	201	$6.00
12	3/13/2017	324	$5.50
13	1/30/2017	201	6.5

<tbody>
</tbody>

Sheet14​

Worksheet Formulas Cell Formula F4 =MAXIFS(A2:A13,B2:B13,E2) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G4 {=MAX(IF(B2:B13=E2,A2:A13))} F5 {=INDEX(C2:C13,MATCH(E2&"|"&F4,B2:B13&"|"&A2:A13,0))} F6 {=IFERROR(INDEX(C2:C13,SMALL(IF(B2:B13=E2,IF(C2:C13<>F5,ROW(C2:C13)-ROW(C2)+1)),1)),"")} F7 {=IF(F6="","",INDEX(A2:A13,MATCH(E2&"|"&F6,B2:B13&"|"&C2:C13,0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



If you don't have the MAXIFS function (F4 formula), the G4 array formula should work.

This is all great information and thanks for sharing it. Now, I tried these formulas because I have a similar issue. I am tracking price changes with each vendor we purchase from for parts and whatnot. Now I have a table similar to Eric. I attempted your instructions and eureka it worked until I sorted my table from descending to ascending order for the date, to test the theory. When I did that the current date changed to previous date and visa versa. Also, for previous cost it was only pulling the original cost that I put in for the first time for the item. Not the date in between the current and original date when the price had changed. ie. Originally purchased item on Aug 14, '07 for .96, then there was a change on Sept 17, 09, purchased at 3.00, and the current date and price is 3.30 and that change was Jan 24, '13. Im assuming the formulas should have pulled my September price, no? So what am I doing wrong? Please help.