formula to match a range(sort) so it matches an external column

formula to match a range(sort) so it matches an external column

Post by steve » Tue, 27 Jun 2006 15:16:01


Hey guys, I'm hoping you can help with this. I have a range from k4 to
p203 that I need to sort so that it matches another range b4 to d291.
The common between the two is U.S cities in the b and L columns.
The range from b4 to d291 has to remain exactly as it is. the rowa in
k4 to p203 need to move up and down to match the other range. There are
also multiple occurances of some cities in the b and l columns, and
there are a few blanks(no city) in the b column. Any ideas how to
accomplish this?
My best guess is this is an offset match or a vlookup. Please send some
suggestions you guys are a lot better at excel than I am. thanks! steveo
 
 
 

formula to match a range(sort) so it matches an external column

Post by Nick Hodg » Tue, 27 Jun 2006 15:54:43

SteveO

It sound like you need a 'custom sort' based on the order in your other
range

If this is correct, go to Tools>Options...>Custom Lists. In the import box
enter the range that holds your cities order and click add. Now go to the
range you want sorting in the same order as this list, select it and then
Data>Sort...>Options... and in 'first key sort order' select your list and
it will sort in this same, custom order.

Hope I understood

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
XXXX@XXXXX.COM