Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Ranges of Data
Hi,
I'm hoping that someone can help me on this. I have a spreadsheet containing customer info and want to sort/unsort the spreadsheet alphabetically by customer name. The problem is that each customer record takes up several rows and columns, and the criteria I want to sort by (Customer Name) is in column B. Is there a way to do a sort by Customer Name (B17, B27, B37, etc) so that the entire customer record (A17:J25, A27:J35, A37:J45, etc) gets moved as well? BEFORE SORT =========== Range Sort by Customer1 data A17:J25 B17 (Vincent) Customer2 data A27:J35 B27 (James) Customer3 data A37:J45 B37 (Cheryl) Customer4 data A47:J55 B47 (Sally) Customer5 data A57:J65 B47 (Bob) AFTER SORT ========== Range Sort by Customer5 data A17:J25 B17 (Bob) Customer3 data A27:J35 B27 (Cheryl) Customer2 data A37:J45 B47 (James) Customer4 data A47:J55 B37 (Sally) Customer1 data A57:J65 B47 (Vincent) Essntially I want to add a button to my spreadsheet to allow a user to sort/unsort ranges of customer records by the customer's name. Any hints or help on this would really be appreciated. Thanks! Kevin |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Ranges of Data
Kevin,
You can do this by making use of another column (helper column) and filling this with values like 1.0, 1.1, 1.2, 1.3 etc, then 2.0, 2.1, 2.2 etc, where a new number is started for each new name and then successive records for the same customer just have 0.1 added on to them. As your records seem to be in blocks of 10 rows, you can enter 1 into K17, then highlight that cell down to the bottom of your data plus one blank row, then Edit | Fill | Series and select Linear with an increment of 0.1. Now you should include column K in your sort area and sort by customer name (B) and by the sequence (K). If you want to get back to the original order, just sort by the sequence. Hope this helps. Pete Kevin Dunn wrote: Hi, I'm hoping that someone can help me on this. I have a spreadsheet containing customer info and want to sort/unsort the spreadsheet alphabetically by customer name. The problem is that each customer record takes up several rows and columns, and the criteria I want to sort by (Customer Name) is in column B. Is there a way to do a sort by Customer Name (B17, B27, B37, etc) so that the entire customer record (A17:J25, A27:J35, A37:J45, etc) gets moved as well? BEFORE SORT =========== Range Sort by Customer1 data A17:J25 B17 (Vincent) Customer2 data A27:J35 B27 (James) Customer3 data A37:J45 B37 (Cheryl) Customer4 data A47:J55 B47 (Sally) Customer5 data A57:J65 B47 (Bob) AFTER SORT ========== Range Sort by Customer5 data A17:J25 B17 (Bob) Customer3 data A27:J35 B27 (Cheryl) Customer2 data A37:J45 B47 (James) Customer4 data A47:J55 B37 (Sally) Customer1 data A57:J65 B47 (Vincent) Essntially I want to add a button to my spreadsheet to allow a user to sort/unsort ranges of customer records by the customer's name. Any hints or help on this would really be appreciated. Thanks! Kevin |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Ranges of Data
HI,
Here is the easy steps. Change the range option as you need. Change B1 if you change the key field. Create command button in exce and double click and it will go the VB editor. Cut and paste into the sub as below Private Sub commandbutton1_click() Range("A1:C4").Select Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Kevin Dunn wrote: Hi, I'm hoping that someone can help me on this. I have a spreadsheet containing customer info and want to sort/unsort the spreadsheet alphabetically by customer name. The problem is that each customer record takes up several rows and columns, and the criteria I want to sort by (Customer Name) is in column B. Is there a way to do a sort by Customer Name (B17, B27, B37, etc) so that the entire customer record (A17:J25, A27:J35, A37:J45, etc) gets moved as well? BEFORE SORT =========== Range Sort by Customer1 data A17:J25 B17 (Vincent) Customer2 data A27:J35 B27 (James) Customer3 data A37:J45 B37 (Cheryl) Customer4 data A47:J55 B47 (Sally) Customer5 data A57:J65 B47 (Bob) AFTER SORT ========== Range Sort by Customer5 data A17:J25 B17 (Bob) Customer3 data A27:J35 B27 (Cheryl) Customer2 data A37:J45 B47 (James) Customer4 data A47:J55 B37 (Sally) Customer1 data A57:J65 B47 (Vincent) Essntially I want to add a button to my spreadsheet to allow a user to sort/unsort ranges of customer records by the customer's name. Any hints or help on this would really be appreciated. Thanks! Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate Several Workbooks into One | Excel Worksheet Functions | |||
Sorting Data From One Column into Multiple Columns | Excel Worksheet Functions | |||
In a 2 Y-Axis chart, how do I assign data ranges to an axis? | Charts and Charting in Excel | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |