finding the largest number from the last row and look up its name.

[Jeff]

Hi,
I have a spreadsheet that contains the following information:
1) Col A - time stamps (number of entries can change).
2) Col B to Col M contain data corresponding to ColA. Each col is
independent of each other. From ColA thru M all have same number of rows.

I need to be able to find the largest number from ColB to ColM on the
last none zone row. Therefore, if there are 10 rows, I am only insterested
in getting the data on the 10th row. Once the value is retrieved, I'll need
to lookup the 1st row on that col which contains the name of the test. say
ColC has the largest number, then it needs to return C1.

I hv tried using offset along w/ countA, i can't quite seem to put them
together.

Thanks,

[Max]

One try ..

Assuming source table is in cols A to M in Sheet1,
with *no* duplicate max numbers in the last row
within cols B to M

In Sheet2
------------
Put in say, B2:

=INDEX(Sheet1!1:1,MATCH(MAX(OFFSET(Sheet1!1:1,COUNTA(Sheet1!A:A)-1,)),OFFSET
(Sheet1!1:1,COUNTA(Sheet1!A:A)-1,),0))

B2 should return what you're after

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:C5F3B657-62C4-4B63-8C1A-6D4CCB3E50C6@microsoft.com...
> Hi,
> I have a spreadsheet that contains the following information:
> 1) Col A - time stamps (number of entries can change).
> 2) Col B to Col M contain data corresponding to ColA. Each col is
> independent of each other. From ColA thru M all have same number of rows.
>
> I need to be able to find the largest number from ColB to ColM on the
> last none zone row. Therefore, if there are 10 rows, I am only
insterested
> in getting the data on the 10th row. Once the value is retrieved, I'll
need
> to lookup the 1st row on that col which contains the name of the test.
say
> ColC has the largest number, then it needs to return C1.
>
> I hv tried using offset along w/ countA, i can't quite seem to put them
> together.
>
> Thanks,
>
>

[Max]

> > 1) Col A - time stamps

Just in case the "time-stamps" in col A in Sheet1 may interfere,
you could try instead in B2 (in Sheet2)

=INDEX(Sheet1!B1:M1,MATCH(MAX(OFFSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,)),OF
FSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,),0))

(It's the same formula as the earlier one essentially,
but now with "Sheet1!B1:M1" replacing "Sheet1!1:1")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Jeff]

Thanks Max, it works after modifing it slightly.

=INDEX(B1:M1,1,MATCH(MAX(OFFSET(Sheet1!B2:M2,COUNTA(Sheet1!A:A)-1,)),OFFSET(Sheet1!B2:M2,COUNTA(Sheet1!A:A)-1,),0))


"Max" wrote:

> > > 1) Col A - time stamps
>
> Just in case the "time-stamps" in col A in Sheet1 may interfere,
> you could try instead in B2 (in Sheet2)
>
> =INDEX(Sheet1!B1:M1,MATCH(MAX(OFFSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,)),OF
> FSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,),0))
>
> (It's the same formula as the earlier one essentially,
> but now with "Sheet1!B1:M1" replacing "Sheet1!1:1")
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

[Max]

Glad to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:FDA2D6F8-321A-4F5A-8688-7966DBC0BA44@microsoft.com...
> Thanks Max, it works after modifing it slightly.

[Aladin Akyurek]

Reluctantly...

=INDEX($B$1:$M$1,MATCH(MAX(INDEX(B:M,MATCH(9.99999999999999E+307,A:A),0)),INDEX(B:M,MATCH(9.99999999999999E+307,A:A),0),0))

In fact, you can have more than 1 test showing the same max score. Just
picking up the first test from row 1 would be unsatisfactory.

Lets try a different approach which takes the ties into account...

To recap: The data range is A:M, with column A from A2 on housing dates
and B1:M1 the headers.

In N2 enter:

=MATCH(9.99999999999999E+307,A:A)

In N3 enter: Data

In N4 enter & copy down:

=IF(ROW()-ROW(N$4)+1<=12,INDEX($B:$M,$N$2,ROW()-ROW(N$4)+1),"")

This formula extracts the last row of data in B:M and puts it in a
vertical range. Note that this vertical range consists of 12 data points.

In O3 enter: Rank

In O4 enter & copy down:

=IF(ISNUMBER(N4),RANK(N4,$N$4:$N$15)+COUNTIF($N$4:N4,N4)-1,"")

This formula ranks the data points of interest.

In P1 enter: 1

We call this the value of N of Top N. Your query involves in fact
extracting a Top 1 test list on the basis of the last scores on the
tests that are taken/applied.

P2:

=MAX(IF(INDEX(N4:N15,MATCH(P1,O4:O15,0))=N4:N15,O4:O15))-P1

which must be confirmed with control+shift+enter instead of the usual enter.

This formula determines the number of ties that Nth top value might
have. This calculation allows for dynamically adjusting the size of the
list of tests which satisfy the condition of being highest.

In P3 enter: Top Test List

In P4 enter & copy down:

=IF(ROW()-ROW(P$4)+1<=$P$1+$P$2,INDEX($B$1:$M$1,MATCH(ROW()-ROW(P$4)+1,$O$4:$O$15,0)),"")

which gives you the desired list of highest scoring tests.


Jeff wrote:
> Hi,
> I have a spreadsheet that contains the following information:
> 1) Col A - time stamps (number of entries can change).
> 2) Col B to Col M contain data corresponding to ColA. Each col is
> independent of each other. From ColA thru M all have same number of rows.
>
> I need to be able to find the largest number from ColB to ColM on the
> last none zone row. Therefore, if there are 10 rows, I am only insterested
> in getting the data on the 10th row. Once the value is retrieved, I'll need
> to lookup the 1st row on that col which contains the name of the test. say
> ColC has the largest number, then it needs to return C1.
>
> I hv tried using offset along w/ countA, i can't quite seem to put them
> together.
>
> Thanks,
>
>