Max Date and Item - How to get in one sheet ?

[captain_2010]

hi,

Have a question, the function of Hlookup.

Below are the details in the sheet.

DataSheet
date -> Feb1,2005 Feb2,2005 Feb3, 2005
item id
1 100 200 100
2 150 100 250
3 490 500 900
4 90 10 20


Output
item Max Date
1 200 Feb2, 2005
2 250 Feb3, 2005
3 900 Feb3, 2005
4 90 Feb1, 2005

How do I get the above output from DataSheet.
Require urgent help.

Captain

 

[Don Guillett]

Maybe it could be as simple as
=max(a:a on row 2 and copy to the right. Then transpose row 1 & 2

 

[Max]

One way to try ..

Assume table below is in Sheet1, A15

Itemid 1-Feb-05 2-Feb-05 3-Feb-05
1 100 200 100
2 150 100 250
3 490 500 900
4 90 10 20

(Dates are in B11 : 1-Feb-05, 2-Feb-05, 3-Feb-05)

In Sheet2
--------
The table below is in A1:C5, with the Itemids listed in A2:A5

Itemid Max Date
1
2
3
4

Put in B2:
=MAX(OFFSET(Sheet1!$B$1,MATCH(A2,Sheet1!$A$2:$A$5,0),,,COUNTA(Sheet1!$1:$1)-
1))

Put in C2:
=INDEX(Sheet1!$B$1:$D$1,MATCH(B2,OFFSET(Sheet1!$B$1:$D$1,MATCH(A2,Sheet1!A:A
,0)-1,),0))
(Format C2 as date)

Select B2:C2, fill down to C5 to populate the table

This returns the desired results, viz.:

Itemid Max Date
1 200 2-Feb-05
2 250 3-Feb-05
3 900 3-Feb-05
4 90 1-Feb-05

Note: It's assumed there's no ties in the maximum values
for any one itemid in Sheet1

 

[Guest]

If the data were in g17 to j21

I would set up a row with
=g17
next to
=max(h17:j17)
and next to it
=INDEX($H$17:$J$17,0,MATCH(MAX(H18:J18),H18:J18,0))
copy these down as far as you need

 

[captain_2010]

Thanks for the formula it worked, though I found another criteria using
Hlookup.
Fact it is used from below a bit strange.

1) Sheet 1 from A15
Itemid 1-Feb-05 2-Feb-05 3-Feb-05
1 100 200 100
2 150 100 250
3 490 500 900
4 90 10 20
on row b6:d6 entered date again as
1-Feb-05 2-Feb-05 3-Feb-05

2) same sheet in column e2: e5
gave row id inclusive of date entered in row b6:d6
i.e. 5,4,3,2 respective rows e2:e5

3) Column F used max ()
for each row i.e. max(b2:d2)
same for respective row
4) Column G used hlookup()
for each row i.e. =HLOOKUP(F2,B2$6,E2,0)

5) Output in Column F and G is there

Question : Hlookup has to take value from first row since date is there
on top
it works only if the date is at the end. Why ?
=HLOOKUP(F2,B2$6,E2,0)
see the E2 it takes from below the value not from top i.e. b1 to d1
instead it takes from b6 : d6.

 

[Max]

Question : Hlookup has to take value from first row

since date is there on top
it works only if the date is at the end. Why ?
=HLOOKUP(F2,B2$6,E2,0)
see the E2 it takes from below the value not from top i.e. b1 to d1
instead it takes from b6 : d6.

In the example above, the lookup row's value in E2
is still counted from the Top row, i.e. *B22*
for the formula above: =HLOOKUP(F2,B2$6,E2,0)
so think there's nothing unusual happening here <g>