Last cell in a row and the one before

[samahiji]

Hi

I have a function that return the last non-blank cell in row:

=LOOKUP(2,1/(A2:BD2<>""),A2:BD2)

I want now to return the 3 cells before it so at the end I have return the 4 last non-blank cells in the row!

Any one could help?

 

[Aladin Akyurek]

What do you have in A2:BD2 - text or number?

 

[samahiji]

What do you have in A2:BD2 - text or number?

They're mixed between date and text.

 

[Aladin Akyurek]

They're mixed between date and text.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td]

3​

[/td][td]jon[/td][td]

7​

[/td][td]

8​

[/td][td]dan[/td][td][/td][td]quo vadis[/td][td][/td][td]paddy[/td][td]damon[/td][td][/td][td]

0.2​

[/td][td][/td][td][/td][/tr]


[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]last non-blank[/td][td][/td][td][/td][td]

0.2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]last 4 non-blank values[/td][td][/td][td][/td][td]quo vadis[/td][td]paddy[/td][td]damon[/td][td]

0.2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

D5, just enter:

=LOOKUP(9.99999999999999E+307,SEARCH("?",A2:BD2),A2:BD2)<strike></strike>

D6, control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($A$2:$BD$2,
  SMALL(IF(COLUMN($A$2:$BD$2)-COLUMN($A$2)+1>=LARGE(IF(1-($A$2:$BD$2=""),
  COLUMN($A$2:$BD$2)-COLUMN($A$2)+1),MIN(4,COUNTIFS($A$2:$BD$2,"<>"))),
  IF(1-($A$2:$BD$2=""),COLUMN($A$2:$BD$2)-COLUMN($A$2)+1)),COLUMNS($D$6:D6))),"")

<strike></strike>

 

[samahiji]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]jon[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]dan[/TD]
[TD][/TD]
[TD]quo vadis[/TD]
[TD][/TD]
[TD]paddy[/TD]
[TD]damon[/TD]
[TD][/TD]
[TD]

0.2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]last non-blank[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0.2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]last 4 non-blank values[/TD]
[TD][/TD]
[TD][/TD]
[TD]quo vadis[/TD]
[TD]paddy[/TD]
[TD]damon[/TD]
[TD]

0.2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


D5, just enter:

=LOOKUP(9.99999999999999E+307,SEARCH("?",A2:BD2),A2:BD2)<strike></strike>

D6, control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($A$2:$BD$2,
  SMALL(IF(COLUMN($A$2:$BD$2)-COLUMN($A$2)+1>=LARGE(IF(1-($A$2:$BD$2=""),
  COLUMN($A$2:$BD$2)-COLUMN($A$2)+1),MIN(4,COUNTIFS($A$2:$BD$2,"<>"))),
  IF(1-($A$2:$BD$2=""),COLUMN($A$2:$BD$2)-COLUMN($A$2)+1)),COLUMNS($D$6:D6))),"")

<strike></strike>

Aladin

It doesn't work for me!
I have XXX rows, then my index should be $A$2:$BD$XXX??

if have the table above in your post, then M1 should have the last value of row 1 (A1:L1), N1:P1 should contain the last 3 cells. And copy down.

 

[Aladin Akyurek]

Aladin

It doesn't work for me!
I have XXX rows, then my index should be $A$2:$BD$XXX??

if have the table above in your post, then M1 should have the last value of row 1 (A1:L1), N1:P1 should contain the last 3 cells. And copy down.

Try to implement the sample and the formulas in a separate sheet to understand the set up...

 

[samahiji]

Try to implement the sample and the formulas in a separate sheet to understand the set up...

I've tried it, but i worked for one row only. I have XXX rows!

 

[Aladin Akyurek]

I've tried it, but i worked for one row only. I have XXX rows!

If it works for one row, it will certainly work for XXX rows. Try to adapt the set up.

 

[samahiji]

If it works for one row, it will certainly work for XXX rows. Try to adapt the set up.

Aladin

That's great!!!

I only changed the index from $A$2:$BD$2 to $A2:$BD2 and work perfect

It's really amazing function, I didn't understand it very well ;D

 

[Aladin Akyurek]

Aladin

That's great!!!

I only changed the index from $A$2:$BD$2 to $A2:$BD2 and work perfect

It's really amazing function, I didn't understand it very well ;D

Great. Thanks for the update.