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

M

Mr James

New Member
Joined
Aug 31, 2017
Messages
1
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).

[TABLE="width: 1023"]
<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>[TR]
[TD="class: xl65, width: 262, bgcolor: transparent"]Time
[/TD]
[TD="width: 529, bgcolor: transparent"]Source[/TD]
[TD="width: 572, bgcolor: transparent"]Message[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:29[/TD]
[TD="bgcolor: transparent"]WN_MANOOD CLOSE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:29[/TD]
[TD="bgcolor: transparent"]CW_PAWS FARM SPS.Logic.Communication State[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:24[/TD]
[TD="bgcolor: transparent"]WN_MANOOD CLOSE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:22[/TD]
[TD="bgcolor: transparent"]Farley 3 SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:21[/TD]
[TD="bgcolor: transparent"]Field Road SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:18[/TD]
[TD="bgcolor: transparent"]Farley 3 SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"] raised[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:14[/TD]
[TD="bgcolor: transparent"]CW_PAWS FARM SPS.Logic.Communication State[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:12[/TD]
[TD="bgcolor: transparent"]SX_HOW CORNER SYDENHAM SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:07[/TD]
[TD="bgcolor: transparent"]PerchField Sarsden Halt TSPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:03[/TD]
[TD="bgcolor: transparent"]SX_RADLEY EAST HAGBOURNE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:00[/TD]
[TD="bgcolor: transparent"]PerchField Sarsden Halt TSPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31/08/2017 09:00[/TD]
[TD="bgcolor: transparent"]DAUGHTER LANE SPS.GENERATOR [/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
</tbody>[/TABLE]


Returning something like this;

[TABLE="width: 826"]
<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>[TR]
[TD="width: 529, bgcolor: transparent"]Source[/TD]
[TD="width: 572, bgcolor: transparent"]Last status[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]WN_MANOOD CLOSE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CW_PAWS FARM SPS.Logic.Communication State[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Farley 3 SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated!



[TABLE="width: 909"]
<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>[TR]
[TD="width: 111, bgcolor: transparent"]Time[/TD]
[TD="width: 529, bgcolor: transparent"]Source[/TD]
[TD="width: 572, bgcolor: transparent"]Message[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:29[/TD]
[TD="bgcolor: transparent"]WN_MANOOD CLOSE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:29[/TD]
[TD="bgcolor: transparent"]CW_PAWS FARM SPS.Logic.Communication State[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:24[/TD]
[TD="bgcolor: transparent"]WN_MANOOD CLOSE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:22[/TD]
[TD="bgcolor: transparent"]Farley 3 SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:21[/TD]
[TD="bgcolor: transparent"]Field Road SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:18[/TD]
[TD="bgcolor: transparent"]Farley 3 SPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"] raised[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:14[/TD]
[TD="bgcolor: transparent"]CW_PAWS FARM SPS.Logic.Communication State[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:12[/TD]
[TD="bgcolor: transparent"]SX_HOW CORNER SYDENHAM SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:07[/TD]
[TD="bgcolor: transparent"]PerchField Sarsden Halt TSPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]raised[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:03[/TD]
[TD="bgcolor: transparent"]SX_RADLEY EAST HAGBOURNE SPS.WET WELL[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:00[/TD]
[TD="bgcolor: transparent"]PerchField Sarsden Halt TSPS.Digital.Wet Well[/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31/08/2017 09:00[/TD]
[TD="bgcolor: transparent"]DAUGHTER LANE SPS.GENERATOR [/TD]
[TD="bgcolor: transparent"]cleared[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Welcome to the MrExcel board!

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


Book1
ABC
1TimeSourceMessage
231/08/2017 9:29WN_MANOOD CLOSE SPS.WET WELLcleared
331/08/2017 9:29CW_PAWS FARM SPS.Logic.Communication Statecleared
431/08/2017 9:24WN_MANOOD CLOSE SPS.WET WELLraised
531/08/2017 9:22Farley 3 SPS.Digital.Wet Wellcleared
631/08/2017 9:21Field Road SPS.Digital.Wet Wellcleared
731/08/2017 9:18Farley 3 SPS.Digital.Wet Wellraised
831/08/2017 9:14CW_PAWS FARM SPS.Logic.Communication Stateraised
931/08/2017 9:12SX_HOW CORNER SYDENHAM SPS.WET WELLraised
1031/08/2017 9:07PerchField Sarsden Halt TSPS.Digital.Wet Wellraised
1131/08/2017 9:03SX_RADLEY EAST HAGBOURNE SPS.WET WELLcleared
1231/08/2017 9:00PerchField Sarsden Halt TSPS.Digital.Wet Wellcleared
1331/08/2017 9:00DAUGHTER LANE SPS.GENERATORcleared
14
15
16SourceLast status
17WN_MANOOD CLOSE SPS.WET WELLcleared
18CW_PAWS FARM SPS.Logic.Communication Statecleared
19Farley 3 SPS.Digital.Wet Wellcleared
Latest Status
Cell Formulas
RangeFormula
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Date based vlookup in excel
Replies
1
Views
376
oldbrewer
O

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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