Pulling last cost of items

J

Jongo1

New Member
Joined
Aug 8, 2016
Messages
24
I have a sheet set up like such:



[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 93"]Item No.[/TD]
[TD="class: xl65, width: 74"]Purch Date[/TD]
[TD="class: xl65, width: 64"] Cost[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="class: xl66, align: right"]1/18/2016[/TD]
[TD="align: right"]12.4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="class: xl66, align: right"]2/1/2016[/TD]
[TD="align: right"]11.12[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="class: xl66, align: right"]3/22/2016[/TD]
[TD="align: right"]43.2[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="class: xl66, align: right"]4/18/2016[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]4/22/2016[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]5/13/2016[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]6/17/2016[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]


It goes on and on for 20,000 rows. What I need to do is extract the most recent cost by date for each item and then I need to extract the date when that last purchase was made for that item. I feel like its a simple formula that I can't think of.
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Assuming this data set, all formulas will need to be adjusted to fit your needs


Book1
ABC
1Item No.Purch DateCost
2Item 118/01/201612.4
3Item 101/02/201611.12
4Item 222/03/201643.2
5Item 218/04/201655
6Item 322/04/2016100
7Item 313/05/2016125
8Item 317/06/2016300
Sheet1



Book1
GHIJ
1Item No.Last PurchasedLast Price PaidNon-Array
2Item 101/02/201611.1211.12
3Item 218/04/20165555
4Item 317/06/2016300300
Sheet1
Cell Formulas
RangeFormula
H2=AGGREGATE(14, 6, ($B$2:$B$8)/($A$2:$A$8=G2), 1)
J2=INDEX($C$2:$C$8, AGGREGATE(14, 6, (ROW($A$2:$A$8)-ROW($A$2)+1)/(($A$2:$A$8=G2)*($B$2:$B$8=H2)), 1))
I2{=INDEX($C$2:$C$8, MATCH(G2&H2, $A$2:$A$8&$B$2:$B$8, 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
mrhstn said:
Assuming this data set, all formulas will need to be adjusted to fit your needs

ABC
Item No.Purch DateCost
Item 1
Item 1
Item 2
Item 2
Item 3
Item 3
Item 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18/01/2016[/TD]
[TD="align: right"]12.4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]43.2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22/04/2016[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]13/05/2016[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]

</tbody>
Sheet1​



GHIJ
Item No.Last PurchasedLast Price PaidNon-Array
Item 1
Item 2
Item 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=AGGREGATE(14, 6, ($B$2:$B$8)/($A$2:$A$8=G2), 1)[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=INDEX($C$2:$C$8, AGGREGATE(14, 6, (ROW($A$2:$A$8)-ROW($A$2)+1)/(($A$2:$A$8=G2)*($B$2:$B$8=H2)), 1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]{=INDEX($C$2:$C$8, MATCH(G2&H2, $A$2:$A$8&$B$2:$B$8, 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]
Click to expand...


Thank you very much, but I noticed a huge flaw in how I reported this. There is another big element to it all.


This is incorrect

Item No.Purch DateCost
Item 1
Item 1
Item 2
Item 2
Item 3
Item 3
Item 3

<tbody>
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]

[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18/01/2016[/TD]
[TD="align: right"]12.4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]43.2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22/04/2016[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]13/05/2016[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]

</tbody>

Each Item has multiple vendors. So it would be last cost by date per vendor per item.

[TABLE="width: 260"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Vendor[/TD]
[TD]Purchase Date[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 2[/TD]
[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 3[/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 4[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 2[/TD]
[TD="align: right"]1/6/2018[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Vendor 5[/TD]
[TD="align: right"]12/2/2017[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Vendor 5[/TD]
[TD="align: right"]10/3/2017[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

I would want to show what the last price was for that specific item for each vendor.
 
Last edited:
Upvote 0
Got it. Used a If(max() for the date requirements and then added the extra criteria to the index array formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
144
Abdo
A
C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
143
CLKinfo
C
A
  • Solved
Extract the names of the Items purchased at different prices
Replies
2
Views
138
ala995
A
F
  • Question Question
Assistance with Runout Dates?
Replies
1
Views
128
Fluff
Fluff
A
  • Solved
adapting code to merge amount for duplicates item on usrform
Replies
4
Views
301
abdo meghari
A

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

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