Sorting by rows with differing numbers of cell entries

[DayleMSCU]

I'm trying to sort each row. Each contains differing numbers of cell entries,
anywhere from 1 to 24. I've done a custom sort by selecting only the cells
containing data on each row then custom sorting etc, but I have over 2,300
individual rows to sort.
I also have problems with the blank cells. If I highlight a whole row they
seem to get placed first in the sort on each row.

The data in each cell starts with numbers like this: 01, 02, 03, 04, etc
with an author's name after, eg. 06 SMITH DM. It's the order of authors for
academic texts, so order is vital. All cells are formatted as numbers.
Hope someone can help.

 

[marysully]

If you haven't tried Filtering the information, give it a try. Under Data,
select the column to filter, choose Autofilter, use the drop down arrow to
get a list of data to sort. It should also eliminate the blank cells.

 

[Jim Cone]

The "blank" cells may actually have something in them that brings them to the top.
They may contain invisible formatting characters or empty text "" (as the result of a formula).
Data from a foreign source (not you) should be cleaned before trying to work with it.

To automate the sorting of multiple individual rows (across) you will need to use a
macro or a commercial sorting utility.
--
Jim Cone
Portland, Oregon USA
([email protected])
3 week free trial of "Special Sort" - just ask for it.




"DayleMSCU" <[email protected]>
wrote in message I'm trying to sort each row. Each contains differing numbers of cell entries,
anywhere from 1 to 24. I've done a custom sort by selecting only the cells
containing data on each row then custom sorting etc, but I have over 2,300
individual rows to sort.
I also have problems with the blank cells. If I highlight a whole row they
seem to get placed first in the sort on each row.

The data in each cell starts with numbers like this: 01, 02, 03, 04, etc
with an author's name after, eg. 06 SMITH DM. It's the order of authors for
academic texts, so order is vital. All cells are formatted as numbers.
Hope someone can help.

 

[Gord Dibben]

06 SMITH DM in a cell cannot possibly be formatted as a number.

If you want a macro to sort across multiple rows here's one from Tom Ogilvy

Since your data is text, you may not get the sort result you want.

Sub SortRows()
'Tom Ogilvy macro
Dim R As Long
Dim lRow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Make the r = 1 whatever the first row of data you want to sort on is.
'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust as
'necessary
'The resize(1, 7) expands the range to 1 cell deep by 7 cells wide

For R = 1 To lRow
With Cells(R, 1).Resize(1, 7)
.Sort Key1:=Cells(R, 2), Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End With
Next R

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Gord Dibben MS Excel MVP