Hi,
I have a sheet which contains values in cells B5 - B17, I need to create an array of the row numbers in order of the cell values in ascending order, I need to ignore any blanks too. This cannot be done on the sheet because of the way the workbook functions.
e.g column B5 down could contain:
Cell Value or
B5 0 50
B6 1 55
B7 2 65
B8 3 100
B9 4 08
B10 5 76
B11 6 22
B12 "" 33
B13 "" 42
B14 "" 66
B15 "" 12
B16 "" 10
I then need an array that has the row numbers in order of the cell values so:
Index Value Or
1 5 9
2 6 16
3 7 15
4 8 11
5 9 12
6 10 13
7 11 5
8 N/A 6
9 N/A 7
10 N/A 14
11 N/A 10
12 N/A 8
What is the simplest method to do this please?
Once I have the row numbers I need to put the values from the array (-4 in each case) in order into the sheet in column A, but I know how to do this part. As column A drives a large lookup table this then re-sorts the sheet and all related sheets into ascending order.
I have a sheet which contains values in cells B5 - B17, I need to create an array of the row numbers in order of the cell values in ascending order, I need to ignore any blanks too. This cannot be done on the sheet because of the way the workbook functions.
e.g column B5 down could contain:
Cell Value or
B5 0 50
B6 1 55
B7 2 65
B8 3 100
B9 4 08
B10 5 76
B11 6 22
B12 "" 33
B13 "" 42
B14 "" 66
B15 "" 12
B16 "" 10
I then need an array that has the row numbers in order of the cell values so:
Index Value Or
1 5 9
2 6 16
3 7 15
4 8 11
5 9 12
6 10 13
7 11 5
8 N/A 6
9 N/A 7
10 N/A 14
11 N/A 10
12 N/A 8
What is the simplest method to do this please?
Once I have the row numbers I need to put the values from the array (-4 in each case) in order into the sheet in column A, but I know how to do this part. As column A drives a large lookup table this then re-sorts the sheet and all related sheets into ascending order.