Sorting Data aplhabetically = Complex??

Paul
Here's a manual way of doing it but a macro could be written to do the same thing :

Insert two columns in front of col A

For each set of data :
- copy the contents of the first cell in the new col C down col A (include the blank row beneath the set)
-put numbers from 1 to 11 in the new col B(include the blank row beneath the set)

Sort by col A > col B
Delete cols A & B

If the sequence within each set is already sorted by one of the other cols, you can achieve the same result as the above by inserting only one column (i.e. the column with numbers would not be necessary).

Celia

Crude, but it works...

Thanks,

Paul.

And here I was, sitting unashamedly and smugly thinking what a cool and neat solution it was !
Celia

Paul,

what you are asking for is rather common. Celia is doing what I and most others would do, and that is to add additional data which you can control. by adding this additional data, you can have a much better control over the data you are working with. One important point with your data is the relative difference of your sets of data (11 rows, counting the blank one).

have you played around with recording a macro in excel? give it a go, then read the code, and you will see why it is important to have consistent sets of data. also, on the "stop recording" toolbar, there is a button called "relative reference". click that and have fun.

cheers,

thomas

Big thanks

what you are asking for is rather common. Celia is doing what I and most others would do, and that is to add additional data which you can control. by adding this additional data, you can have a much better control over the data you are working with. One important point with your data is the relative difference of your sets of data (11 rows, counting the blank one). have you played around with recording a macro in excel? give it a go, then read the code, and you will see why it is important to have consistent sets of data. also, on the "stop recording" toolbar, there is a button called "relative reference". click that and have fun. cheers, thomas

Honestly quys, cheers. Afterall you thought of it not me.

Paul

Big thanks

what you are asking for is rather common. Celia is doing what I and most others would do, and that is to add additional data which you can control. by adding this additional data, you can have a much better control over the data you are working with. One important point with your data is the relative difference of your sets of data (11 rows, counting the blank one). have you played around with recording a macro in excel? give it a go, then read the code, and you will see why it is important to have consistent sets of data. also, on the "stop recording" toolbar, there is a button called "relative reference". click that and have fun. cheers, thomas

Honestly quys, cheers. Afterall you thought of it not me.

Paul

Re: Big thanks

Paul
Here’s a macro to do what you want. This macro will probably only be thought of as crude by persons who are VBA literate. Without such literacy, it will most likely be hailed as the height of sophistication !

Sub SortSets()
Dim TL As Range, LL As Range
Columns("A:B").Insert Shift:=xlToRight
Range("B6").Value = 1
Range(Range("B6"), Range("C65536").End(xlUp). _
Offset(1, -1)).DataSeries Rowcol:= _
xlColumns, Type:=xlLinear, Step:=1
Set TL = Range("C6")
Set LL = TL.Offset(0, -2)
Do Until TL.Value = ""
LL.Value = TL.Value
Range(LL, LL.Offset(10, 0)).FillDown
Set TL = TL.Offset(11, 0)
Set LL = TL.Offset(0, -2)
Loop
Range(Range("A6"), Range("A65536").End(xlUp)) _
.EntireRow.Sort Key1:=Range("A6"), _
Key2:=Range("B6"), Header:=xlNo
Columns("A:B").Delete Shift:=xlToLeft
End Sub

Please note that in addition to the fixed parameters you described, it has been assumed that :-
-The top left hand cell of the first set always starts at cell A6
-There will always be data in the top left hand cell of all the sets
-The last cell in column A of the last set will always contain data

Celia

Bigger Big thanks

Celia, your a star!!

Again, another thank you.

Crude being deleted from my vocab.