Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Sorting of Excel Records (Macros)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Sorting of Excel Records (Macros)
Hi
No need to move to other sheets, just summarise with a Pivot table. Assuming XL2003 Place cursor in tableDataPivot TableFinish 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 ParticipantAdvancedSortDescendingUsing fieldSum of Points Click on W in Category, hold down Control and click on XGroup and SubtotalGroupName 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Sorting of Excel Records | Excel Programming | |||
Excel - Sorting for Duplicate Records | Excel Worksheet Functions | |||
In Excel, sorting columns automatically by clicking column title | Excel Discussion (Misc queries) | |||
automatically delete records w/duplicate address in excel | Excel Discussion (Misc queries) | |||
Sorting Records | Excel Discussion (Misc queries) |