Get Top rows

[JUAN]

Hi,
not sure if this possible. I have a sheet with subtotals
for each product group. My columns are:
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

 

[Guest]

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.

 

[Jamie Collins]

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.

--

 

[Juan]

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