Sorting Values Within a Number of Columns

[dougkpga]

I have data in a worksheet as follows -

ORDER REGION SUB AREA NUMBER VALUE
1 NORTH 7 10 10
2 NORTH 7 5 9
3 NORTH 7 12 7
4 NORTH 7 9 6
5 SOUTH 8 11 5
6 SOUTH 8 3 4
7 SOUTH 8 1 8
8 SOUTH 8 9 10
9 EAST 8 4 3
10 EAST 8 5 6
11 EAST 8 11 8
12 EAST 8 7 7

Is it possible to get this result (having the Values sorted from Largest to Smallest)

without manually selecting the 4 rows of NORTH and Data Sort of VALUE,

then manually selecting the 4 rows of SOUTH as above

And finally the 4 rows of EAST..

To obtain this result (Values sorted within the REGION)

ORDER REGION SUB AREA NUMBER VALUE
1 NORTH 7 10 10
2 NORTH 7 5 9
3 NORTH 7 12 7
4 NORTH 7 9 6
8 SOUTH 8 9 10
7 SOUTH 8 1 8
5 SOUTH 8 11 5
6 SOUTH 8 3 4
11 EAST 8 11 8
12 EAST 8 7 7
10 EAST 8 5 6
9 EAST 8 4 3

[Pete_UK]

You can introduce another column (F) and allocate values according to the region, i.e. 1 for North, 2 for South and 3 for East - use a suitable header for this column (e.g. Region_2). Then select the whole table and choose Sort on the Data menu, using Region_2 as the first sort field (smallest to largest) and Value as the second sort field (Largest to Smallest). You can delete column F when you are done.

Hope this helps.

Pete

[dougkpga]

Hi Pete,

I tried to assign a number to the regions as you suggested using =lookup formula but I get #N/A as a value

Uploaded the worksheet again.

I followed a video exactly - not sure what went wrong.

I made sure none of the cells were locked.

Thanks in advance if you come back

Cheers Doug

[Fluff13]

How about using
=VLOOKUP(H2,$A$16:$B$18,2,0)

[dougkpga]

Thanks Fluff, worked perfectly, never would have worked that out by myself.

[Fluff13]

Glad to help & thanks for the feedback

[dougkpga]

Wonder if I could impose with another question...

Is it possible to get this

2019-10-22_22-51-49.jpg

to this at all

2019-10-22_22-52-57.jpg

Then I could sort the different sub areas as per your previous help

2019-10-22_22-59-19.jpg

Reuploaded sheet with suggestions.

[Fluff13]

How about
=O2&P2

[dougkpga]

Palm slap to the head - I have never seen that formula with the & before .

Thanks so much.

[Fluff13]

You're welcome & thanks for the feedback