Re: Sorting Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Graham Haughs (gmh54_at_tiscali.co.uk)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 22:08:06 +0100

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

"jeff" <anonymous@discussions.microsoft.com> wrote in message
news:537201c4912e$d0068d70$a301280a@phx.gbl...
> 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
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >

Quantcast