# Sorting a list based on ratios

#### AllanSull

##### Board Regular
I have a list of 46 unique items. I have a list of rules that specifies that some items need to be spaced a certain distance apart.

e.g.

46 Items

There are 14 of Item Type A
Item Type A's need to be spaced apart by (46/14)= 3.3 Items

Is there a way I can sort the list by these rules?

e.g.

Item Type A
Other item
Other Item
Other Item
Item Type A
Other Item
Other Item
Other Item
Item Type A

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### caabyyc

##### Board Regular
Not sure what structure for your source data.

1. Type "Item Type A" in A1 and B1

option 1
A.select B1 to B4 (B2:B4 is blank),
B. drag it down to row184 (46x4)
option 2
A.Type following in C1
=INDEX(A:A,MOD(ROW(A4),4)+1,)

B. copy to row184

I have a list of 46 unique items. I have a list of rules that specifies that some items need to be spaced a certain distance apart.

e.g.

46 Items

There are 14 of Item Type A
Item Type A's need to be spaced apart by (46/14)= 3.3 Items

Is there a way I can sort the list by these rules?

e.g.

Item Type A
Other item
Other Item
Other Item
Item Type A
Other Item
Other Item
Other Item
Item Type A

#### AllanSull

##### Board Regular
Not sure what structure for your source data.

1. Type "Item Type A" in A1 and B1

option 1
A.select B1 to B4 (B2:B4 is blank),
B. drag it down to row184 (46x4)
option 2
A.Type following in C1
=INDEX(A:A,MOD(ROW(A4),4)+1,)

B. copy to row184

My source data looks like this;

Item A
Item B
Item B
Item A
Item C
Item D
Item A
Item A

ect ect.

Each letter has its own rule depending on the the size of the sample and the frequency of the letter. In the above example Item A needs to be spaced 2 items apart (8/4). Item B 4 apart, Item C 8 apart and Item D 8 apart.

So the list should look like this;

Item A
Item C
Item A
Item B
Item A
Item D
Item A
Item B

#### AllanSull

##### Board Regular
Does anyone know of a way to achieve this?

Or even any VBA?

#### AllanSull

##### Board Regular
Does anyone know if this is even possible in Excel or if it's beyond its limitations?

#### XOR LX

##### Well-known Member
Hi,

I understand the rules re spacing, though in your Post #3, why does "Item C" come second in the list after "Item A" in your desired results? Why is it not "Item B"?

Also, what if the logic which generates each Item's spacing is not an integer value? Round up? Round down? Round to nearest integer?

Regards

#### AllanSull

##### Board Regular
Hi,

I understand the rules re spacing, though in your Post #3, why does "Item C" come second in the list after "Item A" in your desired results? Why is it not "Item B"?

Also, what if the logic which generates each Item's spacing is not an integer value? Round up? Round down? Round to nearest integer?

Regards

It could be either Item B or Item C in that example as the rules are valid for both. If they are non integer values they would have to be rounded up as it cannot be for example 1.9 items apart, it would have to be 2.

#### XOR LX

##### Well-known Member
"It could be either Item B or Item C in that example as the rules are valid for both."

Sorry - not sure I understand. The "rule" appears to be valid for Item D as well, so are you saying that this could equally be in second position in the results list? So there's nothing at all to dictate the order of returns?

Regards

#### AllanSull

##### Board Regular
Item D could indeed go there. The only rule is that the individual items need to be spaced apart according to their frequency relative to the total items.

Replies
3
Views
113
Replies
9
Views
152
Replies
11
Views
874
Replies
3
Views
246
Replies
18
Views
227

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

### 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.

### Which adblocker are you using?

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

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