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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Branch Code[/TD]
[TD]Product Code[/TD]
[TD]Supplier Name[/TD]
[TD]Over-Stocked[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA80125-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA802-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA805-201-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 1[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ARD55-22K-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 2[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ARDFA20-20K-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 2[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ARDFEAT-11K-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUP 2[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ALTA80125-0.00[/TD]
[TD]SUP 1[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ALTA802-0.00[/TD]
[TD]SUP 1[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ALTA805-201-0.00[/TD]
[TD]SUP 1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ARD55-22K-0.00[/TD]
[TD]SUP 2[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ARDFA20-20K-0.00[/TD]
[TD]SUP 2[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Branch Code[/TD]
[TD]Supplier Name[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]SUP 1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Over-Stocked[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA80125-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA802-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
ETC
ETC

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Branch Code[/TD]
[TD]Supplier Name[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]SUP 2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Over-Stocked[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ARD55-22K-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]ARDFA20-20K-0.00[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]
ETC
ETC

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Branch Code[/TD]
[TD]Supplier Name[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]SUP 1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Over-Stocked[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA80125-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ALTA802-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]
ETC
ETC

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Branch Code[/TD]
[TD]Supplier Name[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]SUP 2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Over-Stocked[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]ARD55-22K-0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]ARDFA20-20K-0.00[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]
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