| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding The MAX Value
My data table looks like this:
ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
Try this:
=SUMPRODUCT(MAX((ID="BOX005")*(Date=20070108)*QTY) ) "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
You can do it as a pivot table. Place your cursor in the middle of your data
and select Data - Pivot Table and Pivot Chart Report... This will bring up a wizard. You can probably just select Finish and it will create a pivot table on a new sheet for you. Drag the ID and Date into the left column and the Quantity into the Middle. Now right click on the ID and select Field Settings - Advanced. Change the Sort order to Desending by Quantity and select Show Top (1). You can remove the subtotaling and do other formatting to make it look a bit better... -- HTH... Jim Thomlinson "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
Thank You. I could not get this to work for me.
My final table needs to list out for each ID, the Data of the Max QTY and the associated Max Qty. Like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 Am I doing something incorrect ? "Jim Thomlinson" wrote: You can do it as a pivot table. Place your cursor in the middle of your data and select Data - Pivot Table and Pivot Chart Report... This will bring up a wizard. You can probably just select Finish and it will create a pivot table on a new sheet for you. Drag the ID and Date into the left column and the Quantity into the Middle. Now right click on the ID and select Field Settings - Advanced. Change the Sort order to Desending by Quantity and select Show Top (1). You can remove the subtotaling and do other formatting to make it look a bit better... -- HTH... Jim Thomlinson "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
Did you get a pivot table created?
Did you add the Id's and Dates to the left column? Did you add the quantities to the middle? -- HTH... Jim Thomlinson "carl" wrote: Thank You. I could not get this to work for me. My final table needs to list out for each ID, the Data of the Max QTY and the associated Max Qty. Like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 Am I doing something incorrect ? "Jim Thomlinson" wrote: You can do it as a pivot table. Place your cursor in the middle of your data and select Data - Pivot Table and Pivot Chart Report... This will bring up a wizard. You can probably just select Finish and it will create a pivot table on a new sheet for you. Drag the ID and Date into the left column and the Quantity into the Middle. Now right click on the ID and select Field Settings - Advanced. Change the Sort order to Desending by Quantity and select Show Top (1). You can remove the subtotaling and do other formatting to make it look a bit better... -- HTH... Jim Thomlinson "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
Sorry in my original description I suggested using Advanced on the ID. I
meant to say on the date field. My Bad... using your data here is the result I got... ID Date Total BOX005 20070108 20612 BOX226 20070111 30335 -- HTH... Jim Thomlinson "carl" wrote: Thank You. I could not get this to work for me. My final table needs to list out for each ID, the Data of the Max QTY and the associated Max Qty. Like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 Am I doing something incorrect ? "Jim Thomlinson" wrote: You can do it as a pivot table. Place your cursor in the middle of your data and select Data - Pivot Table and Pivot Chart Report... This will bring up a wizard. You can probably just select Finish and it will create a pivot table on a new sheet for you. Drag the ID and Date into the left column and the Quantity into the Middle. Now right click on the ID and select Field Settings - Advanced. Change the Sort order to Desending by Quantity and select Show Top (1). You can remove the subtotaling and do other formatting to make it look a bit better... -- HTH... Jim Thomlinson "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding The MAX Value
use advanced filter or some other method to get the unique list of IDs
assuming your main data is in A:C set up a location with your headers (D1:F1?) and your unique list (D2:D3_ in E2 enter =index($B$1:$B$100),match(max(($C$1:$C$100)*($A$1: $A$100=D2),$C$1:$C$100),0) in F2 enter =index($C$1:$C$100),match(max(($C$1:$C$100)*($A$1: $A$100=D2),$C$1:$C$100),0) copy E@:F2 and paste down as far as you have unique IDs "carl" wrote: My data table looks like this: ID Date QTY BOX005 20070103 19626 BOX005 20070104 9618 BOX005 20070105 17465 BOX005 20070108 20612 BOX226 20070109 13310 BOX226 20070110 10859 BOX226 20070111 30335 BOX226 20070112 20015 I am trying to create a table for each ID that shows the Date and Qty of the Maximum Qty - like this table: ID Date QTY BOX005 20070108 20612 BOX226 20070111 30335 The actual data table has around 7000 rows. Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding non-zero value | Excel Discussion (Misc queries) | |||
| Finding last used | Excel Worksheet Functions | |||
| finding value | Excel Discussion (Misc queries) | |||
| finding the "end" | New Users to Excel | |||
| Finding Value | Excel Worksheet Functions | |||
Hybrid Mode
