Finding most recent date in rows of data with an if filter

T

Transmin

New Member
Joined
Nov 29, 2016
Messages
2
Hi,

I have data which has several rows of information on it as below.

I need to find a way to find the latest unit cost for each item (by date). Eg if there are 5 different dates for the same part number to look at the one with the most recent date for that part and then select that cost as the most recent.

What is the best formula to use to get this.

[TABLE="width: 464"]
<tbody>[TR]
[TD]Posting Date[/TD]
[TD]Item No.[/TD]
[TD]Item Description[/TD]
[TD]Unit Cost[/TD]
[/TR]
[TR]
[TD="align: right"]4/07/2016[/TD]
[TD="align: right"]29949[/TD]
[TD]FILLER / BREARTHER - HYDRAULIC TANK[/TD]
[TD="align: right"]64.75[/TD]
[/TR]
[TR]
[TD="align: right"]17/01/2014[/TD]
[TD="align: right"]33162[/TD]
[TD]PUMP - Grease[/TD]
[TD="align: right"]2,455.00[/TD]
[/TR]
[TR]
[TD="align: right"]8/10/2014[/TD]
[TD="align: right"]33162[/TD]
[TD]PUMP - Grease[/TD]
[TD="align: right"]2,572.50[/TD]
[/TR]
[TR]
[TD="align: right"]9/05/2014[/TD]
[TD="align: right"]36360[/TD]
[TD]FILTER - RETURN / FILL[/TD]
[TD="align: right"]660[/TD]
[/TR]
[TR]
[TD="align: right"]11/07/2014[/TD]
[TD="align: right"]36362[/TD]
[TD]FILTER - RETURN[/TD]
[TD="align: right"]596.39[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2014[/TD]
[TD="align: right"]36557[/TD]
[TD]GAUGE - LEVEL / TEMP[/TD]
[TD="align: right"]52.2[/TD]
[/TR]
[TR]
[TD="align: right"]11/07/2014[/TD]
[TD="align: right"]36559[/TD]
[TD]VALVE - SOLENOID[/TD]
[TD="align: right"]802.61[/TD]
[/TR]
[TR]
[TD="align: right"]8/06/2015[/TD]
[TD="align: right"]39218[/TD]
[TD]PLATE - KEEPER[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]29/02/2016[/TD]
[TD="align: right"]39218[/TD]
[TD]PLATE - KEEPER[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]28/04/2014[/TD]
[TD="align: right"]40209[/TD]
[TD]PIGTAIL SCREW AUGER[/TD]
[TD="align: right"]330[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2014[/TD]
[TD="align: right"]42595[/TD]
[TD]PLUG MAGNETIC 1" BSPP[/TD]
[TD="align: right"]53.4[/TD]
[/TR]
[TR]
[TD="align: right"]9/09/2013[/TD]
[TD="align: right"]42738[/TD]
[TD]VALVE - BALL - 1/4"[/TD]
[TD="align: right"]14.3[/TD]
[/TR]
[TR]
[TD="align: right"]28/03/2014[/TD]
[TD="align: right"]42738[/TD]
[TD]VALVE - BALL - 1/4"[/TD]
[TD="align: right"]14.3[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2014[/TD]
[TD="align: right"]42738[/TD]
[TD]VALVE - BALL - 1/4"[/TD]
[TD="align: right"]14.3[/TD]
[/TR]
[TR]
[TD="align: right"]28/01/2016[/TD]
[TD="align: right"]42738[/TD]
[TD]VALVE - BALL - 1/4"[/TD]
[TD="align: right"]14.3[/TD]
[/TR]
[TR]
[TD="align: right"]30/07/2015[/TD]
[TD="align: right"]43246[/TD]
[TD]TEST COUPLING - Pressure[/TD]
[TD="align: right"]42.6[/TD]
[/TR]
[TR]
[TD="align: right"]28/01/2016[/TD]
[TD="align: right"]43246[/TD]
[TD]TEST COUPLING - Pressure[/TD]
[TD="align: right"]42.6[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2014[/TD]
[TD="align: right"]44641[/TD]
[TD]SPACER PLATE[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2014[/TD]
[TD="align: right"]44642[/TD]
[TD]SPACER PLATE[/TD]
[TD="align: right"]82.5[/TD]
[/TR]
[TR]
[TD="align: right"]7/05/2014[/TD]
[TD="align: right"]44767[/TD]
[TD]CONNECTOR SET, ELECTRICAL[/TD]
[TD="align: right"]109.16[/TD]
[/TR]
[TR]
[TD="align: right"]7/05/2014[/TD]
[TD="align: right"]44768[/TD]
[TD]CONNECTOR SET, ELECTRICAL[/TD]
[TD="align: right"]88.15[/TD]
[/TR]
[TR]
[TD="align: right"]5/08/2016[/TD]
[TD="align: right"]44871[/TD]
[TD]INJECTOR - GREASE - 1 OUTLET[/TD]
[TD="align: right"]284.18[/TD]
[/TR]
[TR]
[TD="align: right"]3/01/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]327[/TD]
[/TR]
[TR]
[TD="align: right"]19/02/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]400.15[/TD]
[/TR]
[TR]
[TD="align: right"]7/04/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]382.91[/TD]
[/TR]
[TR]
[TD="align: right"]15/04/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]382.91[/TD]
[/TR]
[TR]
[TD="align: right"]2/05/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]382.91[/TD]
[/TR]
[TR]
[TD="align: right"]7/08/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]382.91[/TD]
[/TR]
[TR]
[TD="align: right"]7/11/2014[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]382.91[/TD]
[/TR]
[TR]
[TD="align: right"]5/08/2016[/TD]
[TD="align: right"]44872[/TD]
[TD]INJECTOR - GREASE - 2 OUTLET[/TD]
[TD="align: right"]433.82[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi,

take a look at this and see if it is what you're after:


Excel 2016 (Windows) 64 bit
ABCDEFG
1Posting DateItem No.Item DescriptionUnit CostItem Description
24-7-201629949FILLER / BREARTHER - HYDRAULIC TANK64,75FILLER / BREARTHER - HYDRAULIC TANK4-7-2016
317-1-201433162PUMP - Grease2,455,00PUMP - Grease8-10-2014
48-10-201433162PUMP - Grease2,572,50FILTER - RETURN / FILL9-5-2014
59-5-201436360FILTER - RETURN / FILL660FILTER - RETURN11-7-2014
611-7-201436362FILTER - RETURN596,39GAUGE - LEVEL / TEMP30-4-2014
730-4-201436557GAUGE - LEVEL / TEMP52,2VALVE - SOLENOID11-7-2014
811-7-201436559VALVE - SOLENOID802,61PLATE - KEEPER29-2-2016
98-6-201539218PLATE - KEEPER25PIGTAIL SCREW AUGER28-4-2014
1029-2-201639218PLATE - KEEPER25PLUG MAGNETIC 1" BSPP14-7-2014
1128-4-201440209PIGTAIL SCREW AUGER330VALVE - BALL - 1/4"28-1-2016
1214-7-201442595PLUG MAGNETIC 1" BSPP53,4TEST COUPLING - Pressure28-1-2016
139-9-201342738VALVE - BALL - 1/4"14,3SPACER PLATE30-4-2014
1428-3-201442738VALVE - BALL - 1/4"14,3CONNECTOR SET, ELECTRICAL7-5-2014
1525-8-201442738VALVE - BALL - 1/4"14,3INJECTOR - GREASE - 1 OUTLET5-8-2016
1628-1-201642738VALVE - BALL - 1/4"14,3INJECTOR - GREASE - 2 OUTLET5-8-2016
1730-7-201543246TEST COUPLING - Pressure42,6
1828-1-201643246TEST COUPLING - Pressure42,6
1930-4-201444641SPACER PLATE65
2030-4-201444642SPACER PLATE82,5
217-5-201444767CONNECTOR SET, ELECTRICAL109,16
227-5-201444768CONNECTOR SET, ELECTRICAL88,15
235-8-201644871INJECTOR - GREASE - 1 OUTLET284,18
243-1-201444872INJECTOR - GREASE - 2 OUTLET327
2519-2-201444872INJECTOR - GREASE - 2 OUTLET400,15
267-4-201444872INJECTOR - GREASE - 2 OUTLET382,91
2715-4-201444872INJECTOR - GREASE - 2 OUTLET382,91
282-5-201444872INJECTOR - GREASE - 2 OUTLET382,91
297-8-201444872INJECTOR - GREASE - 2 OUTLET382,91
307-11-201444872INJECTOR - GREASE - 2 OUTLET382,91
315-8-201644872INJECTOR - GREASE - 2 OUTLET433,82
Sheet1
Cell Formulas
RangeFormula
G2{=LARGE(($C$2:$C$31=F2)*$A$2:$A$31,1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
185
Tetra201
Tetra201
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
341
Fuji
F
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
995
Fluff
Fluff
K
  • Question Question
Copy data from rows with the dates (Monday to Sunday)
Replies
1
Views
61
K_Stevs1
K

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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