Hi
No need to move to other sheets, just summarise with a Pivot table.
Assuming XL2003
Place cursor in table>Data>Pivot Table>Finish
On the PT skeleton that appears on a new page,
Drag Category to the Row area
Drag Participant to the Row area
Drag Points to the Data Area
Double click on Participant>Advanced>Sort>Descending>Using field>Sum of
Points
Click on W in Category, hold down Control and click on X>Group and
Subtotal>Group>Name as Group WX
Do the Same for Y and Z and Name s as Group YZ
This will create a new Category2.
Drag Category2 to the Page area
Drag Category back out of the PT to the Field list.
Now just use the dropdown on Page to select which report you wish to see.
Regards
Roger Govier
Mr. IT wrote:
> Greetings!
>
> I am working in a good IT company under the quality management team. May I
> ask how can I automatically sort an array of records using macros based on
> categories and heirarchy of scores? Then generate a separate sheet for it as
> well?
>
> Here is a simple example of a record array found in sheet1 named as "DATA":
>
> A B C
> PARTICIPANT CATEGORY POINTS
> 1 Malaysia W 87,000
> 2 Indonesia X 12,000
> 3 Singapore Y 98,000
> 4 Thailand Z 15,000
> 5 Hong-Kong Y 58,000
> 6 Japan W 108,000
> 7 Russia X 33,000
> 8 China Z 72,000
>
> I simply need to automatically generate two more sheets: Sheet 2 as
> "GroupsWX" which will contain all entries having categories 'W' and 'X' and
> Sheet 3 as "GroupsYZ" which will contain all entries having categories 'Y'
> and 'Z'.
>
> These two computer-generated sheets will display Columns A and C in
> descending order; without having the need to separate 'W' from 'X' or 'Y'
> from 'Z'.
>
> Thank you very much and God Bless to you, your family, and your company =)\
>
> Regards, Mr. IT
|
Similar Threads
