Help With Sorting

  • Thread starter Thread starter chappo555
  • Start date Start date
C

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 hav
written.

I was trying to sort by surname (a-z). The problem is that this list i
generated elsewhere and contains VLOOKUP functions. Obviously if i hav
less than 100 competitors I end up with some cells appear blank bu
contain VLOOKUP functions. XL sorts them first and I want them sorte
last after the surnames (a-z). I cant add a helper column as this lis
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 bu
that still doesnt work.

ANY HELP WOULD BE GREATLY APPRECIATED

cheers and thanks guys/gals
CHAPPO55
 
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]...
 
Thanks for that, unfortunatly I had thought of that option. The proble
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 co
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 2
sheets start looking for data which wont exist if there is NOT SUPPOSE
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 #erro
values or I have to re write all of the other VLOOKUPS and thats
nightmare because you cant succesfully cut and past VLOOKUP as the
rely on different sort data and col number to find the data they ar
after.
Additionally I cant add a helper column to the right of my print dat
as all of my other calculations I need for splitting ties etc are ther
and they all have combination and absolute cell references combine
along with some more VLOOKUP so they cant be moved so I have print dat
I need to sort - then absolute data (cant be moved) then my helpe
column that Id wish to sort by, so to select my sort area including th
helper column I have to highlight the absolute data and when sort move
it it would screw up my calculations.

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

Any help appreciated.

cheers chapp
 
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
(e-mail address removed)
 
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 !
----------------------------------------------------------------------------
-------------------

in message
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
 
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 selec
the data, sort, first key order, change to custom list that I hav
added through tools, options, custom lists.

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

NEITHER seems to work despite my efforts.

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

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

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

cheers
chappo555
 
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
(e-mail address removed)
 
If the OP's problem is that blanks sort to the top, I don't think that a
custom list will help.
 
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
(e-mail address removed)
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with a vlookup 2
VLookup Function HELP 7
VLOOKUP forumla for Excel HELP! 1
Help with sorting tabs 3
in an Excel database, comparaison between two columns with "almost"the same names => how to search i 1
sorting; blanks at the top; what is a "blank" 1
sorting with custom data 1
error (bug?) with VLOOKUP function 8

Back
Top