Sorting a list based on ratios

[AllanSull]

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

 

[caabyyc]

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]

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]

Does anyone know of a way to achieve this?

 

[AllanSull]

Or even any VBA?

 

[AllanSull]

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

 

[XOR LX]

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]

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]

"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]

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.