Tricky one - finding the last entry for a specific cell and returning a corresponding value.

[Mr James]

Hi,

I need to reference the specific site name on column B, find out which entry for that site is the most recent from column A and return the value in C (whether it is "cleared" or "raised").

There will be duplicates in Column B (Source) but I am only interested in what the most recent status change was using columns A (Time) and C (Message).

Time	Source	Message
31/08/2017 09:29	WN_MANOOD CLOSE SPS.WET WELL	cleared
31/08/2017 09:29	CW_PAWS FARM SPS.Logic.Communication State	cleared
31/08/2017 09:24	WN_MANOOD CLOSE SPS.WET WELL	raised
31/08/2017 09:22	Farley 3 SPS.Digital.Wet Well	cleared
31/08/2017 09:21	Field Road SPS.Digital.Wet Well	cleared
31/08/2017 09:18	Farley 3 SPS.Digital.Wet Well	raised
31/08/2017 09:14	CW_PAWS FARM SPS.Logic.Communication State	raised
31/08/2017 09:12	SX_HOW CORNER SYDENHAM SPS.WET WELL	raised
31/08/2017 09:07	PerchField Sarsden Halt TSPS.Digital.Wet Well	raised
31/08/2017 09:03	SX_RADLEY EAST HAGBOURNE SPS.WET WELL	cleared
31/08/2017 09:00	PerchField Sarsden Halt TSPS.Digital.Wet Well	cleared
31/08/2017 09:00	DAUGHTER LANE SPS.GENERATOR	cleared

<colgroup><col width="262" style="width: 197pt; mso-width-source: userset; mso-width-alt: 9581;"> <col width="529" style="width: 397pt; mso-width-source: userset; mso-width-alt: 19346;"> <col width="572" style="width: 429pt; mso-width-source: userset; mso-width-alt: 20918;"> <tbody> </tbody>

Returning something like this;

Source	Last status
WN_MANOOD CLOSE SPS.WET WELL	cleared
CW_PAWS FARM SPS.Logic.Communication State	cleared
Farley 3 SPS.Digital.Wet Well	cleared

<colgroup><col width="529" style="width: 397pt; mso-width-source: userset; mso-width-alt: 19346;"> <col width="572" style="width: 429pt; mso-width-source: userset; mso-width-alt: 20918;"> <tbody> </tbody>

Any help would be appreciated!

Time	Source	Message
31/08/2017 09:29	WN_MANOOD CLOSE SPS.WET WELL	cleared
31/08/2017 09:29	CW_PAWS FARM SPS.Logic.Communication State	cleared
31/08/2017 09:24	WN_MANOOD CLOSE SPS.WET WELL	raised
31/08/2017 09:22	Farley 3 SPS.Digital.Wet Well	cleared
31/08/2017 09:21	Field Road SPS.Digital.Wet Well	cleared
31/08/2017 09:18	Farley 3 SPS.Digital.Wet Well	raised
31/08/2017 09:14	CW_PAWS FARM SPS.Logic.Communication State	raised
31/08/2017 09:12	SX_HOW CORNER SYDENHAM SPS.WET WELL	raised
31/08/2017 09:07	PerchField Sarsden Halt TSPS.Digital.Wet Well	raised
31/08/2017 09:03	SX_RADLEY EAST HAGBOURNE SPS.WET WELL	cleared
31/08/2017 09:00	PerchField Sarsden Halt TSPS.Digital.Wet Well	cleared
31/08/2017 09:00	DAUGHTER LANE SPS.GENERATOR	cleared

<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="529" style="width: 397pt; mso-width-source: userset; mso-width-alt: 19346;"> <col width="572" style="width: 429pt; mso-width-source: userset; mso-width-alt: 20918;"> <tbody> </tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this, copied down, does what you want.

Book1
	A	B	C
1	Time	Source	Message
2	31/08/2017 9:29	WN_MANOOD CLOSE SPS.WET WELL	cleared
3	31/08/2017 9:29	CW_PAWS FARM SPS.Logic.Communication State	cleared
4	31/08/2017 9:24	WN_MANOOD CLOSE SPS.WET WELL	raised
5	31/08/2017 9:22	Farley 3 SPS.Digital.Wet Well	cleared
6	31/08/2017 9:21	Field Road SPS.Digital.Wet Well	cleared
7	31/08/2017 9:18	Farley 3 SPS.Digital.Wet Well	raised
8	31/08/2017 9:14	CW_PAWS FARM SPS.Logic.Communication State	raised
9	31/08/2017 9:12	SX_HOW CORNER SYDENHAM SPS.WET WELL	raised
10	31/08/2017 9:07	PerchField Sarsden Halt TSPS.Digital.Wet Well	raised
11	31/08/2017 9:03	SX_RADLEY EAST HAGBOURNE SPS.WET WELL	cleared
12	31/08/2017 9:00	PerchField Sarsden Halt TSPS.Digital.Wet Well	cleared
13	31/08/2017 9:00	DAUGHTER LANE SPS.GENERATOR	cleared
14
15
16		Source	Last status
17		WN_MANOOD CLOSE SPS.WET WELL	cleared
18		CW_PAWS FARM SPS.Logic.Communication State	cleared
19		Farley 3 SPS.Digital.Wet Well	cleared
Latest Status

Cell Formulas
Range	Formula
C17	=LOOKUP(9.99E+307,ROW(B$2:B$13)/((A$2:A$13=AGGREGATE(14,6,A$2:A$13/(B$2:B$13=B17),1))*(B$2:B$13=B17)),C$2:C$13)