find last cell with data

[joao56777]

Hello

I dont know how to solve this. I have a lot of lines and columns, with a lot of articles. In same lines I could have OK, or NOK, OR NOK and OK.

What i want is in a column, of the excell sheet, to see the final stake. OR OK OR NOK.

Could you help me?cenas.JPG

[FDibbins]

Hi, welcome to the forum

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

I have changed the title for you - this time

[FDibbins]

Also, pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jtakw]

Hi,

Yes, you should upload a file as suggested, or at least provide a sample in a Table format which potential helpers can copy and paste your data for testing, on top of that, the picture you show is so small, I can't really see what's going on.

I think this is what you mean:

Excel 2016 (Windows) 64 bit

	A	B	C
1			NOK
2		OK	OK
3			OR NOK
4		NOK
5			NOK
6		OK
7		NOK
8			OK
9
10
11
12	Latest Status	NOK	OK

Sheet: Sheet24

Excel 2016 (Windows) 64 bit

	B
12	=LOOKUP(REPT("z",255),B1:B10)

Sheet: Sheet24

Formula copied across.

[joao56777]

Hello,

Sorry for all mess that i cause.

The problem is that i need the resolution in lines, and not in columns,
I've tried this

=if.error(PROCH("OK";H8:DJ8;1;true);"not done")

and this

=if(H8:DN88="OK";"OK";if(H8:DO8="NOK";"NOK";"Not done"))

And no one i have solve the proble. The first on proch, because in the NOK, column in the next 2 columns i have the reasons of their NOK state, and them the excel gave me the last column text

In the second, excel i think, dont read IF, in the line orientation, maybe, i dont know.

I've sent you my sheet. Look to column STATE. If you saw and try to right in the lines, you will se the column change, but the proble is excell gave me the last word of the line.

[Glenn Kennedy]

This is a guess, because I have no idea what your expected results are:

=LOOKUP(REPT("z";255);H3:AC3)

copied down.

If this is incorrect, please TELL US what your expected results are. Amend and re-post your sheet with the expected results.

[joao56777]

Hello Glenn, Many thanks for reply. I've simplified the excel sheel

ProcH is the Portuguese function for LOOKUP, so my function was:
=if.error(lookup("OK";H8:DJ8;1;true);"not done")

In the state, only have to appear, OK, NOK, NOT DONE. nothing more. Lack of fusion or porosity, i dont want to appear.

Look, NOT OK, dont have appear, and excell allways give-me the column beside of NOK, give me for example LACK of FUSION.

And i want, despite the defect that cause his reprove, i want in the state NOK, and not the defect

Look to this another excell sheet

[AliGW]

Is this closer to what you want:

=LOOKUP(2,1/($B3:$W3<>"")*($B$1:$W$1<>"Defeito*"),$B3:$W3)

[joao56777]

Is this closer to what you want:

=LOOKUP(2,1/($B3:$W3<>"")*($B$1:$W$1<>"Defeito*"),$B3:$W3)

Hello, thanks for reply. could you send me an example on my excell sheet?

Regards

[Phuocam]

Try this:

=IFERROR(LOOKUP(2,1/($B3:$W3<>"")/(LEFT($B$1:$W$1,7)<>"Defeito"),$B3:$W3),"NOT DONE")

Or:

=IFERROR(LOOKUP(2,1/($B3:$W3<>"")/ISNUMBER($B$1:$W$1),$B3:$W3),"NOT DONE")

[joao56777]

Try this:

=IFERROR(LOOKUP(2,1/($B3:$W3<>"")/(LEFT($B$1:$W$1,7)<>"Defeito"),$B3:$W3),"NOT DONE")

Or:

=IFERROR(LOOKUP(2,1/($B3:$W3<>"")/ISNUMBER($B$1:$W$1),$B3:$W3),"NOT DONE")

Its not working

[Glenn Kennedy]

You may have simplified it... but you have NOT shown the expected results!!

[Glenn Kennedy]

I think I follow you... use this array formula:

=INDEX(B3:W3,MAX((B3:W3={"OK";"NOK";"Not Done"})*MATCH(COLUMN(B3:W3),COLUMN(B3:W3))))&""

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

See the sheet for EXACT use of , and ; separators in Portuguese...

[Glenn Kennedy]

This gets your "Not Found" response in as well:

=IF(COUNTA(B3:W3)=0,"Not Found",INDEX(B3:W3,MAX((B3:W3={"OK";"NOK";"Not Done"})*MATCH(COLUMN(B3:W3),COLUMN(B3:W3))))&"")

[José Augusto]

Hi

You can try this normal formula that gives the same results as @Glenn in last post (#14)

=IFERROR(INDEX(3:3,AGGREGATE(14,6,COLUMN($B3:$W3)/(($B3:$W3<>"")*(LEFT(B$1:W$1,7)<>"Defeito")),1)),"Não efetuado")

[joao56777]

Hello,

Only today i had some time to put the excell sheet in English and to demonstrate what i want.

On the Column STATUS, I want, per line, to see, If a work made in a different day is OK, NOK, or NÃO EFETUADO (IS NOT DONE YET in English)

In a Line I coud have, OK (the test piece is success in the first test), NOK (the test piece dont pass in first test) and NÃO EFETUADO (not done yet)

I remember that NOK test piece, is necesary to repair and to do another test to see if is positive. IF is positive, the status must be OK, if not is NOK.

The STATUS, must see, the Number (RBP 15 per example), is OK, NOK, or NOT DONE.

Please see my formula by STATUS Column. Sometimes appears erros, and i dont know if is right the way it is.

Could you help me?

[José Augusto]

Hi

You can try this normal formula that gives the same results as @Glenn in last post (#14)

=IFERROR(INDEX(3:3,AGGREGATE(14,6,COLUMN($B3:$W3)/(($B3:$W3<>"")*(LEFT(B$1:W$1,7)<>"Defeito")),1)),"Não efetuado")

PT formula:

=SE.ERRO(ÍNDICE(3:3;AGREGAR(14;6;COL($B3:$W3)/(($B3:$W3<>"")*(ESQUERDA(B$1:W$1;7)<>"Defeito"));1));"Não efetuado")

[José Augusto]

Hi João

Try to use in column J from J8 de following formula

=IFERROR(INDEX(8:8,AGGREGATE(14,6,COLUMN(K8:DI8)/(K8:DI8<>""),1)),"Não efectuado")

Note: I do not see the column STATUS. Column J? (FINAL STATE)

[joao56777]

Yes. Final Ste is the correct column and not status

[joao56777]

Hello,

I think its working very well. But tomorrow i can give you the final answer

Could you explain me the purpose of this formula, explain me step by step.

Regards
joão

[José Augusto]

Hi João

I begin by explaining the third paramparent of the "AGGREGATE" function, that is, COLUMN (K8:DI8)/(K8:DI8<>"").

COLUMN (K8:DI8) provides a vector with the numbers of these columns {11, 12, 13, 14, ... 61, ... 113} whose elements will be divided by

(K8:DI8<>"") that is, in your case, {False, False, .... True, False .... False} (true in column(BI8)=61).

In this division False will be 0 and True will be 1. Thus {11, 12, 13, 14, ... 61, ... 113} / {0, 0, 0, ..., 1, ... 0 } = {# DIV0 !, # DIV0 !, # DIV0 !, # DIV0 !, ..., 61, ..., # DIV0!}

The AGGREGATE function 14 will determine the n largest value, 6 omitting errors, in the result vector {# DIV0 !, # DIV0 !, # DIV0 !, # DIV0 !, ..., 61, ..., # DIV0!} where n=1 represent the first largest value 61. The column 61 row 8 has the value "OK".

I hope I have explained enough

Do not forget to mark this thread as SOLVED.

[joao56777]

Many many thanks for the knowledge.

How do I classify as SOLVED this thread?

Regards my Portughese friend

[José Augusto]

Hi João

To mark the thread solved do the following:

-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Regards.