Listing top 20 values with multiple data sets

[leehuk1982]

Hi all,

This is my first post and I'm in need of some desperate help please.

I have a table with the following data (this is example data):

A	B	C	D
Branch Code	Product Code	Supplier Name	Over-Stocked
10	ALTA80125-0.00 <tbody> </tbody>	SUP 1	20
10	ALTA802-0.00 <tbody> </tbody>	SUP 1	10
10	ALTA805-201-0.00 <tbody> </tbody>	SUP 1	25
10	ARD55-22K-0.00 <tbody> </tbody>	SUP 2	95
10	ARDFA20-20K-0.00 <tbody> </tbody>	SUP 2	150
10	ARDFEAT-11K-0.00 <tbody> </tbody>	SUP 2	250
20	ALTA80125-0.00	SUP 1	45
20	ALTA802-0.00	SUP 1	60
20	ALTA805-201-0.00	SUP 1	20
20	ARD55-22K-0.00	SUP 2	240
20	ARDFA20-20K-0.00	SUP 2	100

<tbody>
</tbody>

I need to create a report that lists the top 20 Over-Stocked products for each Supplier, but the report must be specific to the Branch Code and also specific to the Supplier Name. The report needs to automatically display the top 20 for each branch / supplier / product when we refresh the data set.

To give you an example of what the final report would look like:

Branch Code	Supplier Name
10	SUP 1

<tbody>
</tbody>

Product Code	Over-Stocked
ALTA80125-0.00 <tbody> </tbody>	20
ALTA802-0.00 <tbody> </tbody>	10

<tbody>
</tbody>

ETC
ETC

------NEW PAGE-----

Branch Code	Supplier Name
10	SUP 2

<tbody>
</tbody>

Product Code	Over-Stocked
ARD55-22K-0.00 <tbody> </tbody>	95
ARDFA20-20K-0.00	150

<tbody>
</tbody>

ETC
ETC

------NEW PAGE-----

Branch Code	Supplier Name
20	SUP 1

<tbody>
</tbody>

Product Code	Over-Stocked
ALTA80125-0.00 <tbody> </tbody>	45
ALTA802-0.00 <tbody> </tbody>	60

<tbody>
</tbody>

ETC
ETC

------NEW PAGE-----

Branch Code	Supplier Name
20	SUP 2

<tbody>
</tbody>

Product Code	Over-Stocked
ARD55-22K-0.00 <tbody> </tbody>	240
ARDFA20-20K-0.00	100

<tbody>
</tbody>

ETC
ETC

Any help you could give would be greatly appreciated.

Thanks in advance,
Lee

 

[liveinhope]

how important is it to you that you have a new page on every change of branch and/or supplier ?

If you can live without any "new pages" I have a pivot table solution that gives the information you need ... buts its one continuous page