Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get Top rows
Hi,
not sure if this possible. I have a sheet with subtotals for each product group. My columns a PRODGROUP PART SUMOFPART COUNTOFQUOTE So each product group might have 100 parts or more. Is there a way to get lets say Top 25 rows for each product group? I been trying to do such in Access too, but can't seem to get it. So wondering if its possible in excel. Please advise any input. Really appreciated thanks, Juan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get Top rows
You could try removing the subtotals and aranging the data in a pivot table.
In the colum place the Product Group and the Part. In the data section place the Sum of Part and Count of Quote. When you have the data in the pivot table right click on the Part and select Field Settings. Choose Advanced and you will be able to select Top 25. "JUAN" wrote: Hi, not sure if this possible. I have a sheet with subtotals for each product group. My columns a PRODGROUP PART SUMOFPART COUNTOFQUOTE So each product group might have 100 parts or more. Is there a way to get lets say Top 25 rows for each product group? I been trying to do such in Access too, but can't seem to get it. So wondering if its possible in excel. Please advise any input. Really appreciated thanks, Juan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get Top rows
"Jim Thomlinson" wrote ...
I have a sheet with subtotals for each product group. My columns a PRODGROUP PART SUMOFPART COUNTOFQUOTE So each product group might have 100 parts or more. Is there a way to get lets say Top 25 rows for each product group? I been trying to do such in Access too, but can't seem to get it. You could try removing the subtotals and aranging the data in a pivot table. Here is a query to do the same as described for the Excel pivot e.g. could be used in MS Access: SELECT T1.PRODGROUP, T1.PART FROM Products T1 WHERE 25 = ( SELECT COUNT(*) + 1 FROM Products WHERE PRODGROUP = T1.PRODGROUP AND PART T1.PART ) ORDER BY T1.PRODGROUP, T1.PART DESC; Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get Top rows
Hello Jim/Jamie
sorry for late reply been busy. Can't seem to get it. Should the PROD group and Part by in the row? When I select a field records let say 45 using the PROD and PART in the row, it displays what I want. But when I try to put the whole records 9964 it gets error saying a field in your source data has more uniue item that can be used. Why when I used less records it works? any suggestions, Jamie, will try that query. I tried other query examples but don't seem to work so will continue with this. ANy other info would be great. thanks, Juan -----Original Message----- You could try removing the subtotals and aranging the data in a pivot table. In the colum place the Product Group and the Part. In the data section place the Sum of Part and Count of Quote. When you have the data in the pivot table right click on the Part and select Field Settings. Choose Advanced and you will be able to select Top 25. "JUAN" wrote: Hi, not sure if this possible. I have a sheet with subtotals for each product group. My columns a PRODGROUP PART SUMOFPART COUNTOFQUOTE So each product group might have 100 parts or more. Is there a way to get lets say Top 25 rows for each product group? I been trying to do such in Access too, but can't seem to get it. So wondering if its possible in excel. Please advise any input. Really appreciated thanks, Juan . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto extract data & inserts rows additional rows automatically | Excel Discussion (Misc queries) | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |