Sort list of numeric values to remove peaks and valleys

[Aurillius]

Hi,
I have to move pick locations around in a facility to reduce congestion by spreading high volume locations out.

I hope that someone can help me create some vba that will provide a sort column; this sort column would evenly spread values through a list. See the attached for the desired result.

Hope this is clear, very hard to explain!

 

[SkipVought]

Hi,

Sure. What algorithm or logic do you intend to code in VBA?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Aurillius]

Hi Skip,
The value column will be sorted ascending before I run the code. I want the script to assign a new row # to each of the values in column C which I can manually sort by this same column...C.

Here is a better picture to show what I mean

Mark

 

[SkipVought]

Okay, that's your desired result, but what's the logic that you want to apply given the data? I assume that your actual data is not these 10 rows.

Please explain your logic rules.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Aurillius]

The data in the value column would be numbers ranging from 0 to 89. I will write code to give a standard value of "1" for values less than 20 and then 3 for values greater/equal to 20. The list could be 1 row or 5000 rows but I wouldn't run it on a list less than 50 rows. Then the list would be sorted ascending. Referencing the image above, the logic would recognize that there's 10 rows, 70% are slow (1) and 30% are fast (3). The logic that I don't know how to write would be to use this "weight" data to reassign a new row. The only thing that I can think of is that 2 loops need to be running: loop 1 would be from rows 1 to 7 and loop 2 would be from row 8 to 10...new row numbers would be assigned within each loop but the 3 record loop would assign less frequent??? I'm not sure how to write this.

I did see a page that "split" a number up but it doesn't work in this my example above:


A1 = 7 (for Apples)
A2 = 12 (for Months)

B1 = ROUND(ROW()/$A$2*$A$1,0)
B2 = IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"")

Copy B2 down as needed.

 

[SkipVought]

You don't seem to grasp what logic is.

Why not use the random function in a column to sort on?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Aurillius]

Sorry, I should have answered that question first before explaining. I understand what programming logic is, the problem is that I haven't been able to build the logic...I only know the end result or how I want it to sort. Rand doesn't work as it bunches large numbers together...needs to be spread out more evenly.

 

[SkipVought]

You do realize that there's a difference between the logic you might use to determine a process and programming logic. I am referring to the former, that you need to clearly state the process logic that can then be executed using programming logic via VBA. This will mean that you state the rules that will be used in certain cases.

What you have stated is very confusing. For instance this statement, "I will write code to give a standard value of "1" for values less than 20 and then 3 for values greater/equal to 20" makes no sense at all to me. Maybe you could explain how this will accomplish the kind of sort you desire.

It seems to me that the factors to take into account might be 1) the number of rows of data 2) the number of unique values 3) the number of rows for each unique value. That's just from siting here and sipping my French Roast coffee.

It might also be helpful to post a table example (NOT A PICTURE!!!) that is more representative, since you stated that, "I wouldn't run it on a list less than 50 rows."

You've got to have sound process logic and rules, before a single line of code is generated!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Hmmmmmm???

I took this list of 0 to 29, 132 rows...
[tt]
Value
0
0
0
0
0
0
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
4
4
4
4
5
5
5
5
5
6
6
7
7
7
7
8
8
8
8
9
9
9
9
9
9
9
9
9
10
10
10
10
10
10
11
11
11
11
11
11
12
12
12
12
13
13
13
13
13
14
14
14
14
14
14
15
16
16
16
16
16
16
16
16
16
17
18
18
18
19
19
19
20
20
20
20
21
21
21
21
21
22
22
22
22
22
23
24
24
24
25
25
26
26
26
27
27
28
28
28
28
29
29
29
29
29
29
29
29
29
29
[/tt]
...and used =RAND() in the adjacent column and then sorted on that column with this result:
[tt]
Value
14
16
7
24
29
27
1
10
16
8
12
5
29
9
28
9
14
21
28
15
18
22
11
13
9
22
20
29
8
1
7
11
16
2
13
25
0
24
16
1
10
4
5
6
12
9
18
5
14
7
8
10
3
1
11
7
20
21
26
22
3
1
10
9
12
9
9
0
20
13
16
11
29
11
5
14
26
29
5
20
12
2
3
6
26
10
16
21
29
28
9
14
19
16
13
23
18
14
4
2
29
0
4
16
21
3
24
17
29
2
10
11
29
13
0
25
4
9
28
22
19
8
29
21
27
0
19
2
3
22
16
0
[/tt]

Did you want RANDOM or some PATTERN?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]