Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Max Date and Item - How to get in one sheet ?
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 |
#2
|
|||
|
|||
Maybe it could be as simple as
=max(a:a:) on row 2 and copy to the right. Then transpose row 1 & 2 -- Don Guillett SalesAid Software wrote in message oups.com... 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 |
#3
|
|||
|
|||
One way to try ..
Assume table below is in Sheet1, A1:D5 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 B1:D1 : 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... 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 |
#4
|
|||
|
|||
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 " wrote: 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 |
#5
|
|||
|
|||
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 A1:D5 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:D$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:D$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. |
#6
|
|||
|
|||
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:D$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. *B2:D2* for the formula above: =HLOOKUP(F2,B2:D$6,E2,0) so think there's nothing unusual happening here <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set up a daily spending sheet? | Excel Discussion (Misc queries) | |||
Excel Date Format - Stop? | Excel Worksheet Functions | |||
How do I use the IF function to calculate date | Excel Discussion (Misc queries) | |||
How do I use a" item description" in excel to consolidate totals? | Excel Discussion (Misc queries) | |||
How to write a "Text" or date custom format | Excel Worksheet Functions |