Finding latest data across multiple columns.

Question

Hi

I am looking for a formula to identify when each reference item was last reviewed by month and the slide it was included on.

For context - reference 1 would be a risk item that is reviewed in a forum in Jan. This will then be revisited in Feb but in that forum the review may be in July so it is not included in Mar - June. Therefore, when July comes, I need an easy way to look back and see when it was last included and the slide presented to easily find it and copy it over.

The columns are separated by fields in between such as any commentary from discussions, takeaway actions and when the item will be revisited and they cannot be moved from this order. I haven't populated actual data so this is marked with the XX as it is the slide number needed.

I have put in 3 columns with the data manually:

• N = Combining the month and the slide number. This would be the optimum solution to have in one column.
• O = Just the month.
• P = Last included slide.

Please can you advise on a formula or methodology to generate this without manually inputting - it is over a whole year with over 600 items.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Reference	Jan Slide	Jan Comment	Jan Action	Jan Review	Feb Slide	Feb Comment	Feb Action	Feb Review	Mar Slide	Mar Comment	Mar Action	Mar Review	Last Included - Combined	Last Included Month	Last Included Slide
1	3	xx	xx	xx	6	xx	xx	xx		xx	xx	xx	6 Feb	Feb	6
2	4	xx	xx	xx	7	xx	xx	xx	7	xx	xx	xx	7 Mar	Mar	7
3		xx	xx	xx	12.1	xx	xx	xx	15	xx	xx	xx	15 Mar	Mar	15
4		xx	xx	xx	12.2	xx	xx	xx	16	xx	xx	xx	16 Mar	Mar	16
5	7	xx	xx	xx	7	xx	xx	xx		xx	xx	xx	7 Feb	Feb	7
6	8	xx	xx	xx	8	xx	xx	xx		xx	xx	xx	8 Feb	Feb	8
7	8.2	xx	xx	xx	8.2	xx	xx	xx	17.1	xx	xx	xx	17.1 Mar	Mar	17.1
8	8.3	xx	xx	xx	8.3	xx	xx	xx	17.2	xx	xx	xx	17.2 Mar	Mar	17.2
9		xx	xx	xx	17	xx	xx	xx	19	xx	xx	xx	19 Mar	Mar	19
10	34	xx	xx	xx		xx	xx	xx		xx	xx	xx	34 Jan	Jan	34

Answer 1

Hi,

In cell N2, enter this formula and drag down

=XLOOKUP(TRUE,ISNUMBER(B2:M2),B2:M2&"-"&TEXTBEFORE($B$1:$M$1," "),,,-1)

In cell O2, enter this formula and drag down

=XLOOKUP(TRUE,ISNUMBER(B2:M2),TEXTBEFORE($B$1:$M$1," "),,,-1)

In cell P2, enter this formula and drag down

=XLOOKUP(TRUE,ISNUMBER(B2:M2),$B2:$M2,,,-1)

Hope this helps.

Answer 2

OK, so your version of Excel doesn't support columns Q and S formulae. So we have to stick with column O.

The ref error is because you're trying to refer to the 67th column of a 65 column range!

For my information, could you tell me whether each of the below functions is available in your version of Excel:

LAMBDA

LET

SEQUENCE

Answer 3

Hi

Apologies - I wanted to add.

I have played around with the formula and i have been able to make it work in one small set with:

=INDEX($B14:$AI14,,MAX(IF(LEN(INDEX(IF(TRIM($B14:$AI14)="","",TRIM($B14:$AI14)),,{1,5,9,13,17,21,25,29,33}))>0,{1,5,9,13,17,21,25,29,33}))) & " " & LEFT(INDEX($B$13:$AI$13,,MAX(IF(LEN(INDEX(IF(TRIM($B14:$AI14)="","",TRIM($B14:$AI14)),,{1,5,9,13,17,21,25,29,33}))>0,{1,5,9,13,17,21,25,29,33}))),3)

But when applying it to the full set I am getting a #REF error.

=INDEX($F10:$BR10,,MAX(IF(LEN(INDEX(IF(TRIM($F10:$BR10)="","",TRIM($F10:$BR10)),,{10,17,24,30,35,41,49,58,67}))>0,{10,17,24,30,35,41,49,58,67}&" "&LEFT(INDEX($F$9:$BR$9,MAX(IF(LEN(INDEX(IF(TRIM($F10:$BR10)="","",TRIM($F10:$BR10)),,{10,17,24,30,35,41,49,58,67}))>0,{10,17,24,30,35,41,49,58,67}))),3))))

Answer 4

Hi

Thank you for your response.

I have tried all 3 formulas - in the shared data I am only able to get results with the formula in column O.

Formula in column Q - I get the #NAME error.

Formula in column S - I get the #SPILL error.

I will need to understand the formula in column O further to be able to apply to the true data set as I have tried this and I get a #N/A error.

As mentioned there's columns for each month etc (Apr - Dec) - so going off my full file I have made the formula as I think it should be but guessing I have made an error based on the number of columns.

=INDEX($F10:$BR10,,MAX(IF(LEN(INDEX(IF(TRIM($F10:$BR10)="","",TRIM($F10:$BR10)),,{1,5,9}))>0,{1,5,9}&" "&LEFT(INDEX($F$9:$BR$9,MAX(IF(LEN(INDEX(IF(TRIM($F10:$BR10)="","",TRIM($F10:$BR10)),,{1,5,9}))>0,{1,5,9}))),9))))

Thanks,

Answer 5

Have a look at columns O and Q in the linked-to workbook below as well as the single cell formula at cell S2. Tell me what works and what doesn't and I'll explain further.

The workbook:

https://app.box.com/s/3ndcdv08o2aw1rv2hcbulx7gkk9p2jtb