Wish to sort the first table so that it produces the second table. I use the MID function to extract the number with which to sort however when I try and sort, it puts Alpha10 in front of Alpha2. I can get this to work if I manually enter the sort numbers, what can I do to the MID function so that its length is one character only rather than the 2 which is what I think is throwing this off. There could well be a better way to handle this sort so any suggestions appreciated.
Book3 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Name | P1 Alpha | P10 Alpha | P12 Alpha | P2 Alpha | Name | P1 Alpha | P2 Alpha | P10 Alpha | P12 Alpha | |||
2 | Tom | 3 | 3 | 8 | 9 | Tom | 3 | 9 | 3 | 8 | |||
3 | Jack | 2 | 4 | 9 | 10 | Jack | 2 | 10 | 4 | 9 | |||
4 | Bert | 9 | 7 | 4 | 2 | Bert | 9 | 2 | 7 | 4 | |||
5 | Ken | 3 | 1 | 10 | 1 | Ken | 3 | 1 | 1 | 10 | |||
6 | To Sort by | 1 | 10 | 12 | 2 | To Sort by | 1 | 2 | 10 | 12 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B6:E6 | B6 | =MID(B1,2,2) |