Get right hand non-date value in a row

Question

We have numerous sheets of thickness readings. They take the form of something similar to:

ITEM	POS	BASETHK	BASEDATE	THICKNESS	THK DATE	THICKNESS	THK DATE	THICKNESS	THK DATE	THICKNESS	THK DATE
001	A	4.8	22/05/11	4.9	13/06/23	5.3	25/09/19	5.1	11/06/15	4.8	22/05/11
002	B	8.2	11/06/15	8.1	13/06/23	8.0	25/09/19	8.2	11/06/15	N	22/05/11
003	C	7.4	22/05/11	7.1	13/06/23	N	25/09/19	7.6	11/06/15	7.4	22/05/11

The number of columns after the Item and Position can vary, depending on how many readings we have but, if there isn't a reading one year, the survey date is still entered and an 'N' (for No Reading) is entered.

What I need to get (in the BASETHK and BASEDATE cells) is the rightmost thickness reading for one, and the respective date for the other. I've made the results in the BASE columns bold, and also the cells they're getting their results from.

for the thickness and date columns, there is always one of each for each year's survey, so the BASETHK looks at the rightmost thickness and the BASEDATE looks at the rightmost date for that thickness.

Is this possible? I've Google lots, and tried LOOKUP, INDEX/MATCH and so on, but without real success.

Excel version: 365

Put your desired output to the post. Also mention your Excel version? — Harun24hr, Jun 14 '23 at 08:17
Thanks for your reply. I've updated to say the version. Regarding the output, I need the results (so formulas) in the BASE columns. — Shaun Allan, Jun 14 '23 at 08:23

score 1 · Answer 1 · answered Jun 14 '23 at 08:23

Try this for BASETHK:

=INDEX(2:2,AGGREGATE(14,6,COLUMN($E2:$L2)/($E$1:$L$1="THICKNESS")/($E2:$L2<>"N"),1))

and this for BASEDATE:

=INDEX(2:2,AGGREGATE(14,6,COLUMN($E2:$L2)/($E$1:$L$1="THICKNESS")/($E2:$L2<>"N")+1,1))

You shall specify a proper range if $E2:$L2 or $E$1:$L$1 do not cover the reasonably expected width of data and headers.

Solution developed and tested on Excel 2016. 365 might have a better one so you may wait for others to answer.

score 1 · Answer 2 · answered Jun 14 '23 at 09:02

1

Give a try to the following formula-

=LET(x,IFERROR(XMATCH(FALSE,E2:L2<>"N",0,-1)-1,MAX(COLUMN(E2:L2))-COLUMN($D$1)),CHOOSECOLS(E2:L2,x-1,x))

answered Jun 14 '23 at 09:02

Harun24hr

30,391
4
21
36

1

Nice approach. This is a tweak of your formula to spill it and to make sure that if a `N` is submitted somewhere, it will still search for the minimum date without an `N` submitted next to it: `=LET(a,E2:L4,b,D2:K4,c,SEQUENCE(,COLUMNS(a),0),r,SEQUENCE(ROWS(a)),d,MAP(r,LAMBDA(x,LET(y,MIN(TOROW(INDEX(a,x,)/(ISODD(INDEX(c,1,)))/(INDEX(b,x,)<>"N"),3)),XMATCH(y,INDEX(a,x,))))),INDEX(a,r,d-{1,0}))` – P.b Jun 14 '23 at 12:11
Thank you for this. I'm afraid I can't follow it to see the difference! – Shaun Allan Jun 14 '23 at 12:18
@ShaunAllan Check the values for the 3rd row the sample you updated. My comment version returns `7.4 22/05/11` Haruns version will return `7.1 13/06/23` (values left from the last found `N`). Also with mine it spills all results at once. – P.b Jun 14 '23 at 12:38

David Leal · Answer 3 · 2023-06-17T20:23:28.413

The following is an array solution, that spills the entire result all at once. Put in cell C1, the following formula 1:

=LET(in, E2:L4, REDUCE({"BASETHK","BASEDATE"}, SEQUENCE(ROWS(in)), LAMBDA(ac,s,
 LET(x, WRAPROWS(INDEX(in,s,),2), 
  idx,XMATCH(TRUE, ISNUMBER(TAKE(x,,1)*TAKE(x,,-1)),,-1),
 VSTACK(ac,INDEX(x,idx,))))))

Here is another alternative that doesn't use the REDUCE/VSTACK pattern(1). This pattern is usually less efficient for a large dataset (formula 2):

=LET(in, E2:L4, n,COLUMNS(in), rseq,SEQUENCE(ROWS(in)), cseq,SEQUENCE(n/2,,,2),
   a, CHOOSECOLS(in,cseq), b, CHOOSECOLS(in,cseq+1),
   idx,2*BYROW(ISNUMBER(a*b), LAMBDA(x, XMATCH(TRUE,x,,-1))),
   VSTACK({"BASETHK","BASEDATE"},
     HSTACK(INDEX(in,rseq,idx-1),INDEX(in,rseq,idx))))

Here is the output:

The input range (in) can be extended to the maximum number of columns among all rows, for example it works if you extend it to E2:Z4, if the maximum column from the input range is the column Z.

formula 1: uses the REDUCE/VSTACK pattern(1). On each iteration of REDUCE it selects the row s from the input range (in) via INDEX(in,s,), then invoke on WRAPROWS to wrap the row for every two columns and name x. The first column of x has the BASETHK column values and the second column the BASEDATE values for a given row. Now we need to find using reverse search (-1 in XMATCH), where both columns of x are numeric (idx). Having the index position, we can invoke INDEX(x,idx,) to extract all columns for a given index position. It uses VSTACK to concatenate vertically the result of each iteration, where the first row is the header.

formula 2: rseq represents row number of the input in and cseq, represents the column index positions for odds columns. The names a,b the BASETHK and BASEDATE column values. The condition we are looking for is when a*b is a number. We iterate over all values of BYROW (x) to find when it is TRUE doing a reverse search in XMATCH (-1). The result of BYROW has the index position of such values. It multiplies by 2, since we spit the range by 2. Now we have the index positions idx with respect the initial range in. Finally we use INDEX function to find the corresponding values for each row.

(1) how to transform a table in Excel from vertical to horizontal but with different length

Get right hand non-date value in a row

3 Answers3