Return Value From The Most Recent Cell

[MeGustaXL]

Components are Serial Numbered. I want to know what the current serial number is, but the SN isn't recorded every time. So I need to pick out the most recent recorded SN. Like this:
Serial Number 116
14/12/2015
19/02/2013 116
21/04/2012 Unknown
10/02/2012
14/12/2010 162
03/02/2007 288

I need to be able to look down the list of dates and return the most recent non-blank value in the cell "Serial Number"
Doing this in Excel would be great, but to be able to do it in Access would be the Bee's Knees.

Thanks in advance

Chris

Someday I'll know what I'm donig...damn!

 

[combo]

It' even easier in acces:
1) create Support1 query that pick dates and non-emty fields for Serial Numbers (SN Is Not Null),
2) basing on Support1 query create Support2 query that returns max. date (only one record and one field, use distinct option),
3) in output query link Support1 and Support2 queries via date, you should get not empty field with Serial Number with latest date with SN.

combo

 

[MeGustaXL]

Hmmm thanks for the rapid answer combo, but I'm kind of asking on behalf of a colleague, so I don't speak Access very well.
Apparently, the table is the output of a Crosstab Query, so it's not quite as simple as it first seems.

I can speak fairly fluent Excel, so a method in there would be a start for me ;-)

Chris

Someday I'll know what I'm donig...damn!

 

[combo]

Assuming dates are in column A, SN in column B:
1) insert helper column B (SN will be shifted to C) with formula: [tt]=IF(LEN(C20,C2,0)[/tt],
2) find maximal date with non-empty SN: MAX(B2:B...), where "..." is the last data row,
3) use VLOOKUP to get SN for date found in (2): =VLOOKUP(MAX(B2:B...),B2:C...,2)

combo

 

[SkipVought]

Your two column are in A & B

The formula, entered as an ARRAY FORMULA (ctrl+shift+enter)
[tt]
=INDEX(B1:B6,MATCH(MAX((B1:B6>0)*(A1:A6)),A1:A6,0),1)
[/tt]

Or if you made a Structured Table named tSN
[pre]
DTE SN
12/14/2015
2/19/2013 116
4/21/2012 Unknown
2/10/2012
12/14/2010 162
2/3/2007 288
[/pre]
...the array formula...
[tt]
=INDEX(tSN[SN],MATCH(MAX((tSN[SN]>0)*(tSN[DTE])),tSN[DTE],0),1)
[/tt]
Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

With pivot table you can get SN without any formula. The PT design:
1) date as row field and data field with AVERAGE domain formula,
2) show top 1 as display option,
3) SN as row field with non-empty filtering.


combo

 

[Andrzejek]

And in Access you can ask for this piece of information from the table like this one:

[pre]
MyTable
DTE SN
12/14/2015
2/19/2013 116
4/21/2012 Unknown
2/10/2012
12/14/2010 162
2/3/2007 288
[/pre]
With something like:
[pre]
Select SN
From MyTable
Where DTE = (Select Max(DTE) From MyTable
Where SN is not NULL)
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Tweenster]

Hi All, Thanks for your help so far, Chris was asking on behalf of me. The issue is....
I have a cross tab query with rows of Widgets, their fit dates, the column is the vehicle they are fitted to, and the value is the Serial number. This feeds into a report, please see below. The report is massive, and all I really want is the last fit of each serial number on each vehicle, so I want to put a calc in the widget header that looks down the column and pulls up the serial number, not the blanks. So I can say with some assurance that Car one, has these widgets and serial numbers fitted.

WIDGET	Serial No	        car 1	      Car 2	        Car 3
Widget Header	Widget    = last[CAR1]     = last[CAR2]      = last[CAR3]
	01/01/2012		1236		5651
	01/05/2012			                        4521	
	01/07/2016		5651		
Widget Header	Widget 2= last[CAR1]	= last[CAR2]	= last[CAR3]
	01/01/2012				4523
	01/05/2012		9999		
	01/07/2016			                       7852

I hope this makes sense, thanking you in advance

Mary

 

[SkipVought]

So where's the source data table from which your example is to be generated?

Last[car1], last[car2]... Plz explain last with respect to WHAT?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

....and in addition to my previous question...

how can Widget ? CAR1 have TWO serial numbers for two different dates????

Thought it was the LATEST DATE!

[pre]
WIDGET Serial No car 1 Car 2 Car 3
Widget Header Widget = last[CAR1] = last[CAR2] = last[CAR3]
[highlight #FCE94F]01/01/2012 1236[/highlight] 5651
01/05/2012 4521
[highlight #FCE94F]01/07/2016 5651[/highlight]
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Tweenster]

The source data is an excel linked into a table with a record of maintenance done on a fleet of vehicles, I created a cross tab query with the report date and the serial number fields, and the items they are fitted to and fed that into a report, as above.

On the report I grouped the widgets, and have a header, I want to hide all the data, but on the header have the last instance that the serial was fitted on that item, so =last[car1] brings up the bottom item, but if you refer to the table, I want 9999, not blank, so I want =isnotblank,last[car1] , Is this possible in a report control source?

 

[Tweenster]

Car one can have 2 serial numbers, as items are moved around the fleet of cars, so to fix a widget on car 2, we borrow car 1's widget, what I need is the last time it was seen on car 1.

 

[SkipVought]

Then we need to know what your source data looks like and what all the logic rules are!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Ut seems to me that you must have source data something like this (Structured table named tSN)...

[pre]
DTE SN WIDGET CAR KEY
1/7/2016 1236 1 car1 1car1
1/7/2014 98 1 car1 1car1
1/1/2012 87 1 car1 1car1
1/1/2012 5651 1 car2 1car2
1/5/2012 4521 1 car3 1car3
1/5/2011 97 1 car3 1car3
1/5/2012 9999 2 car1 2car1
1/5/2011 95 2 car1 2car1
1/1/2012 4523 2 car2 2car2
1/1/2011 94 2 car2 2car2
1/7/2016 7852 2 car3 2car3
1/7/2014 96 2 car3 2car3
12/7/2016 1 car1 ZZZZ
1/7/2016 2 car3 ZZZZ
1/5/2013 1 car2 ZZZZ
1/1/2013 1 car3 ZZZZ
1/5/2012 2 car1 ZZZZ
1/1/2012 2 car2 ZZZZ
[/pre]

I added a KEY column with this formula
[tt]
=IF(ISBLANK([@SN]),"ZZZZ",[@WIDGET]&[@CAR])
[/tt]
...and the table is sorted on KEY asc, DTE desc.

Is this a good start?


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Tweenster]

Hi Skip, Thanks for your help, but I think the solution may be easier. All I need is the formula that I can put in the control source of the report header to look down the column and return the last value, not blank. Currently I have =Last([blue car]), but what I need is =Last([blue car])that is more than 0, or is not null.

Many Thanks
Mary

 

[combo]

This looks like reverse engineering, you have a report (access?) and need to process it. Assuming you have this already done, any change in the report will force you to start work from scratch. Chris pointed that the data is an output from the crosstab query and access is a possible tool. In this case it is not a problem to write another query (or a series of queries) that returns a table similar to this in recent Skip's post and process it. You should have everything necessary for this in the entry for the crosstab query. If you are forced to use excel, again, Skip showed a solution in his first answer, you only need to adjust references.

combo

 

[SkipVought]

Mary, I'm retired now, but during my career, someone, maybe my boss, would give me a report that someone else generated and ask me to do some further analysis in order to generate some other set of data.

This was ALWAYS more difficult that the effort to generate the original report. ALWAYS! That's the case we have here. Whether in Access or Excel, the solution will be cleaner working from scratch rather than putting a patch on and existing process.

Now that I have a clearer picture of what you're shooting for, check this out. Again a Structure Table (tSN) in Excel with a new column KEY with formula: =IF(ISBLANK([@SN]),"ZZZZ",[@WIDGET]&[@CAR]) and sorted by KEY asc, DTE desc as shown:

[pre]
DTE SN WIDGET CAR KEY
[highlight #FCE94F]1/7/2016 1236 1 car1 1car1[/highlight]
1/7/2014 98 1 car1 1car1
1/1/2012 87 1 car1 1car1
[highlight #FCE94F]1/1/2012 5651 1 car2 1car2[/highlight]
[highlight #FCE94F]1/5/2012 4521 1 car3 1car3[/highlight]
1/5/2011 97 1 car3 1car3
[highlight #FCE94F]1/5/2012 9999 2 car1 2car1[/highlight]
1/5/2011 95 2 car1 2car1
[highlight #FCE94F]1/1/2012 4523 2 car2 2car2[/highlight]
1/1/2011 94 2 car2 2car2
[highlight #FCE94F]1/7/2016 7852 2 car3 2car3[/highlight]
1/7/2014 96 2 car3 2car3
[highlight #FCE94F]12/7/2016[/highlight] 1 car1 ZZZZ
1/7/2016 2 car3 ZZZZ
[highlight #FCE94F]1/5/2013[/highlight] 1 car2 ZZZZ
[highlight #FCE94F]1/1/2013[/highlight] 1 car3 ZZZZ
[highlight #FCE94F]1/5/2012[/highlight] 2 car1 ZZZZ
[highlight #FCE94F]1/1/2012[/highlight] 2 car2 ZZZZ
[/pre]

Here's your report, where the static data is a unique list of WIDGETs vertically and CARs horizontally. The report table starts in cell G1:

[pre]
WIDGET car1 car2 car3
1 [highlight #729FCF]1236 5651 4521[/highlight]
2 [highlight #729FCF]9999 4523 7852[/highlight]
[/pre]

...the formula: [highlight #729FCF]=INDEX(OFFSET(tSN[SN],MATCH($G2&H$1,tSN[KEY],0)-1,0,COUNTIF(tSN[KEY],$G2&H$1),1),1,1)[/highlight]

The key to this solution is the helper column in the source data, the formula in the source data and the sort in the source data, which places the data that you need in the first position of each WIDGET&CAR. Notice that the data from the highlighted rows appear in the report and that the highlighted dates with blank SNs are all later than the dates for the data in the report as per your requirements.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}