# Placing counties in order (Rank maybe?)

#### Darren Bartrup

##### Well-known Member
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:
Code:
``=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
FGHIJK
21LutonAvonEssexNottsDevonSuffolk
22324498384343
23
24LutonNottsDevonEssexAvonSuffolk
25338498244343
Sheet1

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Mike Blackman

##### Well-known Member
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

##### Well-known Member

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!

Replies
1
Views
143
Replies
14
Views
356
Replies
0
Views
138
Replies
0
Views
551
Replies
7
Views
315

1,190,911
Messages
5,983,525
Members
439,848
Latest member
timmyo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back