Sorting Ranges of Data

[Kevin Dunn]

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

 

[Pete_UK]

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

 

[Vandy]

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