Shift array values down the the array to make room for new value in 1st position of the array?

Allesgut

New Member
Joined
Oct 13, 2022
Messages
2
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
I am trying to make a pseudo-sorter to sort values from 1 array and paste the results to a second array smaller by shifting values down the second array
since the position of the original array list is important, I can't just sort the original list and paste the top 3 smallest values to the new array. This method is only permissible.
So lets say for the size of array2 = 3
Code:
array2 (1,1) = 2
array2 (2,1) = 4
array2 (3,1) = 5

NEW SMALLER VALUE FOUND in array1 = 1
Code:
array2 (1,1) = 1
array2 (2,1) = 2
array2 (3,1) = 4

as it iterates thru the 1st array, it checks if its smaller than the value of the 1st value of the second array, then it shifts all the value within second array by 1 (the last number of 2nd array remove out of the array) to make room in the first position of array2 for that smaller value.
This only goes thru the array1 once (does not loop to find the "true" smallest)
It skips every "nthsteps" when iterating thru array1 for the sake of efficiency.
This is the code I got so far, yet it doesn't really work:

VBA Code:
Function pseudo_sorter_smallest(range1 As Range, size As Integer, nthsteps As Integer)
    Dim array1() As Double
    Dim array2() As Double
 
    ncount = range1.Cells.Count
 
    ReDim array1(1 To ncount, 1 To 1) As Double
    ReDim array2(1 To size, 1 To 2) As Double
 
    For i = 1 To ncount
        array1(i, 1) = range1(i, 1)
    Next i
 
    array2(1, 1) = array1(1, 1)
    array2(1, 2) = i
 
    For i = 1 To ncount Step nthsteps
        If array1(i, 1) < array2(1, 1) Then
        For j = 1 To size - 1
            array2(j + 1, 1) = array2(j, 1)
            array2(j + 1, 2) = array2(j, 2)
        Next j
            array2(1, 1) = array1(i, 1)
            array2(1, 2) = i
        End If
    Next i

    pseudo_sorter_smallest = array2()
End Function


End Function
The thing is im trying to make the second array size dynamic and able to to change based on number of values 2nd array requires.
I am no expert in VBA or coding in general, so I'd appreciate if the solution is similar to how I write my code.
the desired output is like this in excel for Pseudo__sorter_smallest(data, 3, 5):

valuesposition of values in data
124
220
43

any inputs will be much appreciated
Thanks in advance!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
to add:
the range "data" is filled with 100 entries of random values:

values | position of values in data
-----------------------------------
1 | 24
2 | 20
4 | 3

*position in of values in data means that e.g. value 1 can located in the 24th row of the data range.
 
Upvote 0
to add:
the range "data" is filled with 100 entries of random values:

values | position of values in data
-----------------------------------
1 | 24
2 | 20
4 | 3

*position in of values in data means that e.g. value 1 can located in the 24th row of the data range.
Like this?
VBA Code:
Function pseudo_sorter_smallest(range1 As Range,size as long, nthsteps As long)
    Dim array1() As variant
    Dim array2() As Double
    ReDim array2(1 To size, 1 To 2) As Double
    
    array1=range1.value
    array2(1, 1) = array1(1, 1)
    array2(1, 2) = 1 'Did you mean 1 or to have it be equal to 1 more than the number of rows in the source range? I set it to 1
  
    For i = lbound(array1) to ubound(array1) step nthsteps
        if array1(i,1) < array2(1,1) Then
            j=ubound(array2,1)
            do while j > lbound(array2,1)
                array2(j,1)=array2(j-1,1)
                array2(j,2)=array2(j-1,2)
                j=j-1
            loop
            array2(1,1) = array1(i,1)
            array2(1,2) = i
        end if
    next i
    pseudo_sorter_smallest = array2
End Function
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top