Sorting Query

[Graham Haughs]

I have a range of data, say A1:N150. The data in column A reads Field 1,
Field 2, Field 3 etc and so on up to a possible of Field 150. However these
can be in any order at the start and there may be blanks. As you know if the
data was sorted by this column the it would turn out Field 1, Field 10,
Field 11 etc.
I got round this by entering in column N1, =If(Len(A1)=0,200,Len(A1)) , then
copying this down to N150.
I then sort the data by column N, then sort by Column A. This then puts the
fields in the correct order, and puts the blank entries at the bottom. I am
not sure if this is a perfect solution but it works in this situation. OK
the problem is that some of the fields are split and may be called Field 4
Top in one entry and Field 4 Bottom in another entry. Using the method above
these would then be sorted down to the bottom of the list, albeit together.
It is liveable with but not ideal as I would have liked them to be in the
normal sequence after Field 13 for example.
I would be grateful for any thoughts on this.

Kind regards
Graham Haughs
Turriff, Scotland

 

[jeff]

Hi,

Try this instead:

=IF(ISERROR(FIND(" ",A1,7)),LEN(A1),FIND(" ",A1,7)-1)

(now the only problem is switching Top and Bottom!!)

jeff

 

[Graham Haughs]

Thanks Jeff
I added as shown to pick out the blanks and this switched them to the
bottom.

=IF(LEN(A1)=0,200,IF(ISERROR(FIND(" ",A1,7)),LEN(A1),FIND(" ",A1,7)-1))

Very grateful for your help.
Graham