VBA create array of row numbers in order of cell contents

[M0Dark]

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.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also why can't you have a formula in col A that does exactly what you want?

 

[M0Dark]

Morning Fluff,

The data is a series of test points, I can't have a formula in column A because on the sheet column A has drop down menus which let the user select what order to put the test points in, this is needed because sometimes a different order is required, but most of the time Ascending is the preferred layout so I am trying to have it set the order ascending when the test values are imported from a CSV file.
When the test point number in a row of column A is changed the sheet looks up the corresponding test data and populates columns B to BI

Testbed New Jon.xlsm
	A	B
2	Test Point	Flow (SI)
3		Q
4	N/A	m^3/s
5	1	0.00690
6	2	0.01385
7	3	0.02209
8	4	0.02838
9	5	0.02919
10	6	0.03445
11	7	0.04258
12	8	0.00020
13	9
14	10
15	11
16	12
Test Data Corrections

 

[Fluff]

Ok, how about

Sub M0Dark()
   Dim Ary As Variant
   
   With Range("B5", Range("B" & Rows.Count).End(xlUp))
      Ary = Evaluate(Replace("sortby(filter(row(@)-4,@<>""""),filter(@,@<>""""))", "@", .Address))
   End With
   Range("A5").Resize(UBound(Ary)).Value = Ary
End Sub

 

[M0Dark]

Turns out I could just place an extra column on another sheet and use match & small to find the positions then use that to do the initial sort. Thanks anyway.

 

[Fluff]

Glad you sorted it & thanks for the feedback.