Function:Help With Sorting

[chappo555]

Gudday to all XL gurus.

I need some help on using the SORT function.
I have a list of 100 competitors in a scoring spreadsheet that I have written.

I was trying to sort by surname (a-z). The problem is that this list is generated elsewhere and contains VLOOKUP functions. Obviously if i have less than 100 competitors I end up with some cells appear blank but contain VLOOKUP functions. XL sorts them first and I want them sorted last after the surnames (a-z). I cant add a helper column as this list relates to over 48,000 forumulaes and functions that are already added. If I add a helper column I have to re write all those formulaes.

I tried adding a custom list in the tools - options - custom lists but that still doesnt work.

ANY HELP WOULD BE GREATLY APPRECIATED

cheers and thanks guys/gals
CHAPPO555

[Roger Govier]

Hi

I can't see why you would need to re-write all your formulae, but anyway
a helper column can be placed anywhere on the sheet. Place it to the far
right of your block of data, and assuming your column with the Names is
column A, in this new helper column enter
=IF(A2="",REPT("Z",255),A2)
and copy down
Any cells returning Null from your Vlookup's will be converted to a
string of Z's.
Now mark the whole block of data (including your new column to the far
right) and sort ascending on the new column.

--
Regards

Roger Govier


"chappo555" <[email protected]>
wrote in message
news:[email protected]...
>
> Gudday to all XL gurus.
>
> I need some help on using the SORT function.
> I have a list of 100 competitors in a scoring spreadsheet that I have
> written.
>
> I was trying to sort by surname (a-z). The problem is that this list
> is
> generated elsewhere and contains VLOOKUP functions. Obviously if i
> have
> less than 100 competitors I end up with some cells appear blank but
> contain VLOOKUP functions. XL sorts them first and I want them sorted
> last after the surnames (a-z). I cant add a helper column as this list
> relates to over 48,000 forumulaes and functions that are already
> added.
> If I add a helper column I have to re write all those formulaes.
>
> I tried adding a custom list in the tools - options - custom lists but
> that still doesnt work.
>
> ANY HELP WOULD BE GREATLY APPRECIATED
>
> cheers and thanks guys/gals
> CHAPPO555
>
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
> http://www.excelforum.com/member.php...o&userid=36060
> View this thread:
> http://www.excelforum.com/showthread...hreadid=559598
>

[chappo555]

Thanks for that, unfortunatly I had thought of that option. The problem is that Column A is used as a source for over 20 other pages of data. All of those pages rely on =if(isblank(name in col A),"****",vlookup,name in col A,one of 20 other sheets,other cell,row)) (example used).
If I change data in Column A to z rept, 255 then all of the other 20 sheets start looking for data which wont exist if there is NOT SUPPOSED to be a name allocated to that cell in Col A.
If I use that forumula you described I will end up with #name or #error values or I have to re write all of the other VLOOKUPS and thats a nightmare because you cant succesfully cut and past VLOOKUP as they rely on different sort data and col number to find the data they are after.
Additionally I cant add a helper column to the right of my print data as all of my other calculations I need for splitting ties etc are there and they all have combination and absolute cell references combined along with some more VLOOKUP so they cant be moved so I have print data I need to sort - then absolute data (cant be moved) then my helper column that Id wish to sort by, so to select my sort area including the helper column I have to highlight the absolute data and when sort moves it it would screw up my calculations.

Reading this back sounds like I've painted myself into a corner doesnt it !!

Any help appreciated.

cheers chappo

[Nick Hodge]

Why doesn't a custom sort list work? It should, how are you applying it?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"chappo555" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for that, unfortunatly I had thought of that option. The problem
> is that Column A is used as a source for over 20 other pages of data.
> All of those pages rely on =if(isblank(name in col
> A),"****",vlookup,name in col A,one of 20 other sheets,other cell,row))
> (example used).
> If I change data in Column A to z rept, 255 then all of the other 20
> sheets start looking for data which wont exist if there is NOT SUPPOSED
> to be a name allocated to that cell in Col A.
> If I use that forumula you described I will end up with #name or #error
> values or I have to re write all of the other VLOOKUPS and thats a
> nightmare because you cant succesfully cut and past VLOOKUP as they
> rely on different sort data and col number to find the data they are
> after.
> Additionally I cant add a helper column to the right of my print data
> as all of my other calculations I need for splitting ties etc are there
> and they all have combination and absolute cell references combined
> along with some more VLOOKUP so they cant be moved so I have print data
> I need to sort - then absolute data (cant be moved) then my helper
> column that Id wish to sort by, so to select my sort area including the
> helper column I have to highlight the absolute data and when sort moves
> it it would screw up my calculations.
>
> Reading this back sounds like I've painted myself into a corner doesnt
> it !!
>
> Any help appreciated.
>
> cheers chappo
>
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
> http://www.excelforum.com/member.php...o&userid=36060
> View this thread: http://www.excelforum.com/showthread...hreadid=559598
>

[RagDyeR]

I don't think you understand exactly what Roger suggested!

You're *not changing* anything in Column A.

You're adding a (helper) column, in an out-of-the-way location, but still
contiguous to the data, which configures *itself* according to the data in
Column A, and then using *that* 'helper' column as the sort key.

Do you follow?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"chappo555" <[email protected]> wrote
in message news:[email protected]...

Thanks for that, unfortunatly I had thought of that option. The problem
is that Column A is used as a source for over 20 other pages of data.
All of those pages rely on =if(isblank(name in col
A),"****",vlookup,name in col A,one of 20 other sheets,other cell,row))
(example used).
If I change data in Column A to z rept, 255 then all of the other 20
sheets start looking for data which wont exist if there is NOT SUPPOSED
to be a name allocated to that cell in Col A.
If I use that forumula you described I will end up with #name or #error
values or I have to re write all of the other VLOOKUPS and thats a
nightmare because you cant succesfully cut and past VLOOKUP as they
rely on different sort data and col number to find the data they are
after.
Additionally I cant add a helper column to the right of my print data
as all of my other calculations I need for splitting ties etc are there
and they all have combination and absolute cell references combined
along with some more VLOOKUP so they cant be moved so I have print data
I need to sort - then absolute data (cant be moved) then my helper
column that Id wish to sort by, so to select my sort area including the
helper column I have to highlight the absolute data and when sort moves
it it would screw up my calculations.

Reading this back sounds like I've painted myself into a corner doesnt
it !!

Any help appreciated.

cheers chappo


--
chappo555
------------------------------------------------------------------------
chappo555's Profile:
http://www.excelforum.com/member.php...o&userid=36060
View this thread: http://www.excelforum.com/showthread...hreadid=559598

[chappo555]

Thanks for the replies but still cant figure it out

Nick HODGE - In answer to your question:
I am trying to record a macro (im very basic with macros) and select the data, sort, first key order, change to custom list that I have added through tools, options, custom lists.

I tried a custom list of just a to z but that didnt work and then I tried reversing the default sort order under help seach ie a to z and then all the other characters.

NEITHER seems to work despite my efforts.

I am trying to sort alphabetically but all the surnames come from other sheets and some will appear blank (depending on the number of competitors that I actually have ranging from 5 to 100) but these cells contain forumulaes.

In answer to the other answer. If contiguous means that it must be attached to the data, ie my data goes from Col A to Col AT (which it does), I already have helper colums in AU to ZC inclusive ( all these are if(isblank(xx),"**",vlookup etc.

So I cant add a col at AT and I cant insert one at Col A as it will stuff up over 19,000 formulaes that I already have

cheers
chappo555.

[Nick Hodge]

To get a custom sort to work, you should rearrange your 100 names in to the
order you want and then add that list to the custom sort. Then when you use
the custom sort it will sort in the same order as your preset list

e.g a list loaded as a custom one like so

Nick Hodge
Chip Pearson
John Walkenbach
Bob Phillips
Gord Dibben

would sort a list like this

Nick Hodge
Chip Pearson
Gord Dibben
Nick Hodge
Bob Phillips
John Walkenbach
Bob Phillips

Like this

Nick Hodge
Nick Hodge
Chip Pearson
John Walkenbach
Bob Phillips
Bob Phillips
Gord Dibben

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"chappo555" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the replies but still cant figure it out
>
> Nick HODGE - In answer to your question:
> I am trying to record a macro (im very basic with macros) and select
> the data, sort, first key order, change to custom list that I have
> added through tools, options, custom lists.
>
> I tried a custom list of just a to z but that didnt work and then I
> tried reversing the default sort order under help seach ie a to z and
> then all the other characters.
>
> NEITHER seems to work despite my efforts.
>
> I am trying to sort alphabetically but all the surnames come from other
> sheets and some will appear blank (depending on the number of
> competitors that I actually have ranging from 5 to 100) but these cells
> contain forumulaes.
>
> In answer to the other answer. If contiguous means that it must be
> attached to the data, ie my data goes from Col A to Col AT (which it
> does), I already have helper colums in AU to ZC inclusive ( all these
> are if(isblank(xx),"**",vlookup etc.
>
> So I cant add a col at AT and I cant insert one at Col A as it will
> stuff up over 19,000 formulaes that I already have
>
> cheers
> chappo555.
>
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
> http://www.excelforum.com/member.php...o&userid=36060
> View this thread: http://www.excelforum.com/showthread...hreadid=559598
>

[Nick Hodge]

To get a custom sort to work, you should rearrange your 100 names in to the
order you want and then add that list to the custom sort. Then when you use
the custom sort it will sort in the same order as your preset list

e.g a list loaded as a custom one like so

Nick Hodge
Chip Pearson
John Walkenbach
Bob Phillips
Gord Dibben

would sort a list like this

Nick Hodge
Chip Pearson
Gord Dibben
Nick Hodge
Bob Phillips
John Walkenbach
Bob Phillips

Like this

Nick Hodge
Nick Hodge
Chip Pearson
John Walkenbach
Bob Phillips
Bob Phillips
Gord Dibben

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"chappo555" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the replies but still cant figure it out
>
> Nick HODGE - In answer to your question:
> I am trying to record a macro (im very basic with macros) and select
> the data, sort, first key order, change to custom list that I have
> added through tools, options, custom lists.
>
> I tried a custom list of just a to z but that didnt work and then I
> tried reversing the default sort order under help seach ie a to z and
> then all the other characters.
>
> NEITHER seems to work despite my efforts.
>
> I am trying to sort alphabetically but all the surnames come from other
> sheets and some will appear blank (depending on the number of
> competitors that I actually have ranging from 5 to 100) but these cells
> contain forumulaes.
>
> In answer to the other answer. If contiguous means that it must be
> attached to the data, ie my data goes from Col A to Col AT (which it
> does), I already have helper colums in AU to ZC inclusive ( all these
> are if(isblank(xx),"**",vlookup etc.
>
> So I cant add a col at AT and I cant insert one at Col A as it will
> stuff up over 19,000 formulaes that I already have
>
> cheers
> chappo555.
>
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
> http://www.excelforum.com/member.php...o&userid=36060
> View this thread: http://www.excelforum.com/showthread...hreadid=559598
>

[Roger Govier]

Hi

If you are at column ZC, then you must be using XP2007.
Since that ends at column XFD, why can't you add a column at ZD?

--
Regards

Roger Govier


"chappo555" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks for the replies but still cant figure it out
>
> Nick HODGE - In answer to your question:
> I am trying to record a macro (im very basic with macros) and select
> the data, sort, first key order, change to custom list that I have
> added through tools, options, custom lists.
>
> I tried a custom list of just a to z but that didnt work and then I
> tried reversing the default sort order under help seach ie a to z and
> then all the other characters.
>
> NEITHER seems to work despite my efforts.
>
> I am trying to sort alphabetically but all the surnames come from
> other
> sheets and some will appear blank (depending on the number of
> competitors that I actually have ranging from 5 to 100) but these
> cells
> contain forumulaes.
>
> In answer to the other answer. If contiguous means that it must be
> attached to the data, ie my data goes from Col A to Col AT (which it
> does), I already have helper colums in AU to ZC inclusive ( all these
> are if(isblank(xx),"**",vlookup etc.
>
> So I cant add a col at AT and I cant insert one at Col A as it will
> stuff up over 19,000 formulaes that I already have
>
> cheers
> chappo555.
>
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
> http://www.excelforum.com/member.php...o&userid=36060
> View this thread:
> http://www.excelforum.com/showthread...hreadid=559598
>

[Debra Dalgleish]

If the OP's problem is that blanks sort to the top, I don't think that a
custom list will help.

Nick Hodge wrote:
> To get a custom sort to work, you should rearrange your 100 names in to the
> order you want and then add that list to the custom sort. Then when you use
> the custom sort it will sort in the same order as your preset list
>
> e.g a list loaded as a custom one like so
>
> Nick Hodge
> Chip Pearson
> John Walkenbach
> Bob Phillips
> Gord Dibben
>
> would sort a list like this
>
> Nick Hodge
> Chip Pearson
> Gord Dibben
> Nick Hodge
> Bob Phillips
> John Walkenbach
> Bob Phillips
>
> Like this
>
> Nick Hodge
> Nick Hodge
> Chip Pearson
> John Walkenbach
> Bob Phillips
> Bob Phillips
> Gord Dibben
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[Nick Hodge]

Debra

I missed the blanks bit as it appeared originally that he just wanted his
names sorted by last name. It is still confusing why he can't use a helper
column also

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> If the OP's problem is that blanks sort to the top, I don't think that a
> custom list will help.
>
> Nick Hodge wrote:
>> To get a custom sort to work, you should rearrange your 100 names in to
>> the order you want and then add that list to the custom sort. Then when
>> you use the custom sort it will sort in the same order as your preset
>> list
>>
>> e.g a list loaded as a custom one like so
>>
>> Nick Hodge
>> Chip Pearson
>> John Walkenbach
>> Bob Phillips
>> Gord Dibben
>>
>> would sort a list like this
>>
>> Nick Hodge
>> Chip Pearson
>> Gord Dibben
>> Nick Hodge
>> Bob Phillips
>> John Walkenbach
>> Bob Phillips
>>
>> Like this
>>
>> Nick Hodge
>> Nick Hodge
>> Chip Pearson
>> John Walkenbach
>> Bob Phillips
>> Bob Phillips
>> Gord Dibben
>>
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>

[Debra Dalgleish]

Contiguous means it has to be part of the same table (no blank column or
row separating it), but it doesn't have to be adjacent to the section
with the data. It could be added to the far right of the table, in
column ZD, as Roger suggested.

chappo555 wrote:
> Thanks for the replies but still cant figure it out
>
> Nick HODGE - In answer to your question:
> I am trying to record a macro (im very basic with macros) and select
> the data, sort, first key order, change to custom list that I have
> added through tools, options, custom lists.
>
> I tried a custom list of just a to z but that didnt work and then I
> tried reversing the default sort order under help seach ie a to z and
> then all the other characters.
>
> NEITHER seems to work despite my efforts.
>
> I am trying to sort alphabetically but all the surnames come from other
> sheets and some will appear blank (depending on the number of
> competitors that I actually have ranging from 5 to 100) but these cells
> contain forumulaes.
>
> In answer to the other answer. If contiguous means that it must be
> attached to the data, ie my data goes from Col A to Col AT (which it
> does), I already have helper colums in AU to ZC inclusive ( all these
> are if(isblank(xx),"**",vlookup etc.
>
> So I cant add a col at AT and I cant insert one at Col A as it will
> stuff up over 19,000 formulaes that I already have
>
> cheers
> chappo555.
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html