Placing counties in order (Rank maybe?)

[Darren Bartrup]

The excerpt below is showing what I would like my spreadsheet to show.
The top two rows contain the raw data - this can be anywhere between 1 and 26 different counties (which is why the formula in the bottom row is using OFFSET in the SMALL function).
The bottom row is showing the figures in ascending numerical value.
What I'm trying to do is match the county name to the corresponding figure in the sorted list. I can't use a simple match to find the correct column numbers as there will be duplicate numbers.
I thought I'd cracked it by using the RANK function:

=IF(F24="","",INDEX(OFFSET($F21,0,0,1,6),,RANK(F25,OFFSET($F25,0,0,1,6),COLUMN())))

But the 3 is ranked joint 1st which knocks everything out.

Any idea how to match the figure in the bottom row to the corresponding column of figures at the top?

All help much appreciated as usual,
Darren.

Book2
	F	G	H	I	J	K
21	Luton	Avon	Essex	Notts	Devon	Suffolk
22	3	244	98	3	84	343
23
24	Luton	Notts	Devon	Essex	Avon	Suffolk
25	3	3	84	98	244	343
Sheet1

 

[Mike Blackman]

Hi,

You could simply sort the table;


Data - Sort - Options - Sort Left to Right - Select the Row with the number values in and click ok.

Should work fine.

 

[Darren Bartrup]

Thanks for the reply Schwarzmanne.

Unfortunately I can't follow that route as the finished workbook won't be used by me (ask a manager to sort??? Do you realise how difficult that is?)

The finished sheet will also have approximately 400 seperate blocks of data, all of which will be in varying orders and each needs to be sorted seperately. The numbers will also vary when population is taken into account (view per 1000 population, per 10000 population, over 18's, 18-64, etc). So the whole thing can change depending which options the user ticks.

So my Front Page worksheet has a list of all the areas, which are grouped depending on the users whim and the raw data needs sorting in order so the charts will show them in ascending order.
I take it Excel 07 has a sort function, but I'm stuck with 03 for now

I'm now worried about the processor overhead - how long will it take for the spreadsheet to recalculate. Might it be better to use VBA - then I can show a better progress indicator.
So many choices!