Max function to work on text in cells

[Legacy 33122]

I have 10 odd columns that contain text, not numbers, thus I can't use the max function.

There is only one entry in each column for the ten columns per item. I want to return the cell with text entered into one cell at the end of the range. I could use 7 imbedded if statments and then place another 3 if statements into another cell to test if the cells in the range are above 0, however, this is clumsy! Is there another way to do it?

Any help would be appreciated.

 

[Harvey]

what is in the other 9 columns(cells)?

 

[Aladin Akyurek]

Looks like you want the last text entry. If so:

=LOOKUP(REPT("z",255),Range)

If not, try to elaborate and include the data range in your follow up.

 

[KenWright]

Or if you actually wanted the address of the Cell it was in:-

=ADDRESS(MATCH(REPT("z",255),A1:A15),COLUMN(A1),4)
if the cell address was to go at the bottom of the columns

or

=ADDRESS(ROW(A1),MATCH(REPT("z",255),A1:K1),4)
if the cell address was to go to the right of the columns

 

[Aladin Akyurek]

Or if you actually wanted the address of the Cell it was in:-

=ADDRESS(MATCH(REPT("z",255),A1:A15),COLUMN(A1),4)
if the cell address was to go at the bottom of the columns

or

=ADDRESS(ROW(A1),MATCH(REPT("z",255),A1:K1),4)
if the cell address was to go to the right of the columns

Easier...

=CELL("Address",INDEX(Range,MATCH(REPT("z",255),Range)))

If needed, de-dollarize the result with SUBSTITUTE.

 

[Legacy 33122]

I have never posted an actual version of a spreadsheet to this website before, how can I do it? I have tried all the responses and none seems to do the trick. Obviously it would be simpler if you could see the data in question?

 

[Aladin Akyurek]

I have never posted an actual version of a spreadsheet to this website before, how can I do it? I have tried all the responses and none seems to do the trick. Obviously it would be simpler if you could see the data in question?

Here with an exhibit of how the formulas work, presupposing that we understood the task...

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		xcs	dra	mta		kyy
3
4
5
6	Lasttextvalue	kyy
7	Address	$F$2
8
Sheet1

Formulas...

B6:

=LOOKUP(REPT("z",255),B2:M2)

Fetches the last text value from the range B2:M2.

B7:

=CELL("Address",INDEX(B2:M2,MATCH(REPT("z",255),B2:M2)))

Returns the address of the last text value from the range B2:M2.

Is this what you were looking for?

 

[Legacy 33122]

Sort of.

The columns B through to M would only have one entry in each row, i.e. in row 2 there would only be one text entry, say kyy in row2, column F. The next row down would only contain one cell entry, i.e. say dra in row3, column C.

Having identified this I would want whatever this value was per row for the range of B through to M to be inserted into a column at the end of the range, i.e. in Column N per row.

Hope this makes it a little clearer?

Appreciate the help!

 

[Aladin Akyurek]

Sort of.

The columns B through to M would only have one entry in each row, i.e. in row 2 there would only be one text entry, say kyy in row2, column F. The next row down would only contain one cell entry, i.e. say dra in row3, column C.

Having identified this I would want whatever this value was per row for the range of B through to M to be inserted into a column at the end of the range, i.e. in Column N per row.

Hope this makes it a little clearer?

Appreciate the help!

The formula will work per row. Why don't you just try by putting the formula in N2 and copying it down?

 

[Legacy 33122]

Your right, I'm an idiot. It did work! Apologies for that.

Just so I can get my head around your formula, I understand what a lookup is and the repeat function for the repitition of designated text, I don't understand the use of "z" and the 255?