Ordering Exercise

T

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,907
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is my data:

1718821509617.png


and these are the results:

931
935
934
936
915
914
916
954
956
3154
3156
3156
3546
1546
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
The logic is as follows:

Row 2 totals 100.

Starting with the largest number in row 2, (cell J2), look for the next largest number (cell D2).

Do these two sum over 50? If yes:

note down the combination 3,9 because 3 is in cell D1 and 9 is in cell J1.

If no:

look for next highest value (cell B2). Do these three sum over 50? If yes, note down combo 1,3,9.

If no:

look for next highest value, etc.

Can someone please show me how to automate this process with VBA?

Thanks

Note the results SHOULD be in ascending numerical order within itself, so instead of 931, it should be 139. therefore the complete set of results should be:
 
Last edited:
Upvote 0
139
359
349
369
159
149
169
459
569
1345
1356
1356
3456
1456
 
Upvote 0
Here's a formula option.
Book1
ABCDEFGHIJKLMNOPQR
1Index12345678910ValueIndexSumValue combinationsIndex combinations
2Value1520101510301511515, 20, 10, 151, 3, 4, 5
32032015, 20, 10, 15, 101, 3, 4, 5, 6
415, 201, 33515, 20, 10, 15, 10, 301, 3, 4, 5, 6, 9
51041015, 20, 10, 15, 301, 3, 4, 5, 9
615, 101, 42515, 20, 10, 101, 3, 4, 6
720, 103, 43015, 20, 10, 10, 301, 3, 4, 6, 9
815, 20, 101, 3, 44515, 20, 10, 301, 3, 4, 9
91551515, 20, 151, 3, 5
1015, 151, 53015, 20, 15, 101, 3, 5, 6
1120, 153, 53515, 20, 15, 10, 301, 3, 5, 6, 9
1215, 20, 151, 3, 55015, 20, 15, 301, 3, 5, 9
1310, 154, 52515, 20, 10, 301, 3, 6, 9
1415, 10, 151, 4, 54015, 20, 301, 3, 9
1520, 10, 153, 4, 54515, 10, 15, 101, 4, 5, 6
1615, 20, 10, 151, 3, 4, 56015, 10, 15, 10, 301, 4, 5, 6, 9
171061015, 10, 15, 301, 4, 5, 9
1815, 101, 62515, 10, 10, 301, 4, 6, 9
1920, 103, 63015, 10, 301, 4, 9
2015, 20, 101, 3, 64515, 15, 10, 301, 5, 6, 9
2110, 104, 62015, 15, 301, 5, 9
2215, 10, 101, 4, 63515, 10, 301, 6, 9
2320, 10, 103, 4, 64020, 10, 15, 103, 4, 5, 6
2415, 20, 10, 101, 3, 4, 65520, 10, 15, 10, 303, 4, 5, 6, 9
2515, 105, 62520, 10, 15, 303, 4, 5, 9
2615, 15, 101, 5, 64020, 10, 10, 303, 4, 6, 9
2720, 15, 103, 5, 64520, 10, 303, 4, 9
2815, 20, 15, 101, 3, 5, 66020, 15, 10, 303, 5, 6, 9
2910, 15, 104, 5, 63520, 15, 303, 5, 9
3015, 10, 15, 101, 4, 5, 65020, 10, 303, 6, 9
3120, 10, 15, 103, 4, 5, 65520, 303, 9
3215, 20, 10, 15, 101, 3, 4, 5, 67010, 15, 10, 304, 5, 6, 9
333093010, 15, 304, 5, 9
3415, 301, 94510, 10, 304, 6, 9
3520, 303, 95015, 10, 305, 6, 9
3615, 20, 301, 3, 965
3710, 304, 940
3815, 10, 301, 4, 955
3920, 10, 303, 4, 960
4015, 20, 10, 301, 3, 4, 975
4115, 305, 945
4215, 15, 301, 5, 960
4320, 15, 303, 5, 965
4415, 20, 15, 301, 3, 5, 980
4510, 15, 304, 5, 955
4615, 10, 15, 301, 4, 5, 970
4720, 10, 15, 303, 4, 5, 975
4815, 20, 10, 15, 301, 3, 4, 5, 990
4910, 306, 940
5015, 10, 301, 6, 955
5120, 10, 303, 6, 960
5215, 20, 10, 301, 3, 6, 975
5310, 10, 304, 6, 950
5415, 10, 10, 301, 4, 6, 965
5520, 10, 10, 303, 4, 6, 970
5615, 20, 10, 10, 301, 3, 4, 6, 985
5715, 10, 305, 6, 955
5815, 15, 10, 301, 5, 6, 970
5920, 15, 10, 303, 5, 6, 975
6015, 20, 15, 10, 301, 3, 5, 6, 990
6110, 15, 10, 304, 5, 6, 965
6215, 10, 15, 10, 301, 4, 5, 6, 980
6320, 10, 15, 10, 303, 4, 5, 6, 985
6415, 20, 10, 15, 10, 301, 3, 4, 5, 6, 9100
Sheet4
Cell Formulas
RangeFormula
B1:K1B1=SEQUENCE(1,10)
M2:M64M2=DROP(MID(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1)
N2:N64N2=DROP(MID(REDUCE(0,TOCOL(IFS(B2:K2<>"",B1#),2),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1)
O2:O64O2=DROP(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a+c))),1)
Q2:R35Q2=SORT(FILTER(M2:N64,O2#>=50),2)
Dynamic array formulas.
 
Upvote 0
Cubist said:
Here's a formula option.
Book1
ABCDEFGHIJKLMNOPQR
1Index12345678910ValueIndexSumValue combinationsIndex combinations
2Value1520101510301511515, 20, 10, 151, 3, 4, 5
32032015, 20, 10, 15, 101, 3, 4, 5, 6
415, 201, 33515, 20, 10, 15, 10, 301, 3, 4, 5, 6, 9
51041015, 20, 10, 15, 301, 3, 4, 5, 9
615, 101, 42515, 20, 10, 101, 3, 4, 6
720, 103, 43015, 20, 10, 10, 301, 3, 4, 6, 9
815, 20, 101, 3, 44515, 20, 10, 301, 3, 4, 9
91551515, 20, 151, 3, 5
1015, 151, 53015, 20, 15, 101, 3, 5, 6
1120, 153, 53515, 20, 15, 10, 301, 3, 5, 6, 9
1215, 20, 151, 3, 55015, 20, 15, 301, 3, 5, 9
1310, 154, 52515, 20, 10, 301, 3, 6, 9
1415, 10, 151, 4, 54015, 20, 301, 3, 9
1520, 10, 153, 4, 54515, 10, 15, 101, 4, 5, 6
1615, 20, 10, 151, 3, 4, 56015, 10, 15, 10, 301, 4, 5, 6, 9
171061015, 10, 15, 301, 4, 5, 9
1815, 101, 62515, 10, 10, 301, 4, 6, 9
1920, 103, 63015, 10, 301, 4, 9
2015, 20, 101, 3, 64515, 15, 10, 301, 5, 6, 9
2110, 104, 62015, 15, 301, 5, 9
2215, 10, 101, 4, 63515, 10, 301, 6, 9
2320, 10, 103, 4, 64020, 10, 15, 103, 4, 5, 6
2415, 20, 10, 101, 3, 4, 65520, 10, 15, 10, 303, 4, 5, 6, 9
2515, 105, 62520, 10, 15, 303, 4, 5, 9
2615, 15, 101, 5, 64020, 10, 10, 303, 4, 6, 9
2720, 15, 103, 5, 64520, 10, 303, 4, 9
2815, 20, 15, 101, 3, 5, 66020, 15, 10, 303, 5, 6, 9
2910, 15, 104, 5, 63520, 15, 303, 5, 9
3015, 10, 15, 101, 4, 5, 65020, 10, 303, 6, 9
3120, 10, 15, 103, 4, 5, 65520, 303, 9
3215, 20, 10, 15, 101, 3, 4, 5, 67010, 15, 10, 304, 5, 6, 9
333093010, 15, 304, 5, 9
3415, 301, 94510, 10, 304, 6, 9
3520, 303, 95015, 10, 305, 6, 9
3615, 20, 301, 3, 965
3710, 304, 940
3815, 10, 301, 4, 955
3920, 10, 303, 4, 960
4015, 20, 10, 301, 3, 4, 975
4115, 305, 945
4215, 15, 301, 5, 960
4320, 15, 303, 5, 965
4415, 20, 15, 301, 3, 5, 980
4510, 15, 304, 5, 955
4615, 10, 15, 301, 4, 5, 970
4720, 10, 15, 303, 4, 5, 975
4815, 20, 10, 15, 301, 3, 4, 5, 990
4910, 306, 940
5015, 10, 301, 6, 955
5120, 10, 303, 6, 960
5215, 20, 10, 301, 3, 6, 975
5310, 10, 304, 6, 950
5415, 10, 10, 301, 4, 6, 965
5520, 10, 10, 303, 4, 6, 970
5615, 20, 10, 10, 301, 3, 4, 6, 985
5715, 10, 305, 6, 955
5815, 15, 10, 301, 5, 6, 970
5920, 15, 10, 303, 5, 6, 975
6015, 20, 15, 10, 301, 3, 5, 6, 990
6110, 15, 10, 304, 5, 6, 965
6215, 10, 15, 10, 301, 4, 5, 6, 980
6320, 10, 15, 10, 303, 4, 5, 6, 985
6415, 20, 10, 15, 10, 301, 3, 4, 5, 6, 9100
Sheet4
Cell Formulas
RangeFormula
B1:K1B1=SEQUENCE(1,10)
M2:M64M2=DROP(MID(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1)
N2:N64N2=DROP(MID(REDUCE(0,TOCOL(IFS(B2:K2<>"",B1#),2),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1)
O2:O64O2=DROP(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a+c))),1)
Q2:R35Q2=SORT(FILTER(M2:N64,O2#>=50),2)
Dynamic array formulas.
Click to expand...
Thanks for your response.

Which columns are your results?

I expected only 12 combinations:



139​
359​
349​
369​
159​
149​
169​
459​
569​
1345​
1356​
3456​


Cubist said:
Column Q is the value combinations and R is the corresponding index.
Click to expand...


Note: I am NOT seeking to return all combinations which are greater than 50.

I am only seeking a subset of the combinations.

My logic is as follows:

Start with largest number and check if it is > 50.

If yes, record the combination, then start again but with 2nd largest number, etc.

If not, combine largest with 2nd largest and check. If not combine third, etc.
 
Last edited:
Upvote 0
Column Q is the value combinations and R is the corresponding index.
 
Upvote 0
It can be combined into a single formula.
Book1
ABCDEFGHIJKLMNO
1Index12345678910Value CombinationsIndex CombinationsSum
2Value15201015103015, 20, 151, 3, 550
315, 10, 15, 101, 4, 5, 650
420, 303, 950
510, 10, 304, 6, 950
615, 20, 10, 101, 3, 4, 655
720, 10, 15, 103, 4, 5, 655
815, 10, 301, 4, 955
910, 15, 304, 5, 955
1015, 10, 301, 6, 955
1115, 10, 305, 6, 955
1215, 20, 10, 151, 3, 4, 560
1315, 20, 15, 101, 3, 5, 660
1420, 10, 303, 4, 960
1515, 15, 301, 5, 960
1620, 10, 303, 6, 960
1715, 20, 301, 3, 965
1820, 15, 303, 5, 965
1915, 10, 10, 301, 4, 6, 965
2010, 15, 10, 304, 5, 6, 965
2115, 20, 10, 15, 101, 3, 4, 5, 670
2215, 10, 15, 301, 4, 5, 970
2320, 10, 10, 303, 4, 6, 970
2415, 15, 10, 301, 5, 6, 970
2515, 20, 10, 301, 3, 4, 975
2620, 10, 15, 303, 4, 5, 975
2715, 20, 10, 301, 3, 6, 975
2820, 15, 10, 303, 5, 6, 975
2915, 20, 15, 301, 3, 5, 980
3015, 10, 15, 10, 301, 4, 5, 6, 980
3115, 20, 10, 10, 301, 3, 4, 6, 985
3220, 10, 15, 10, 303, 4, 5, 6, 985
3315, 20, 10, 15, 301, 3, 4, 5, 990
3415, 20, 15, 10, 301, 3, 5, 6, 990
3515, 20, 10, 15, 10, 301, 3, 4, 5, 6, 9100
Sheet4
Cell Formulas
RangeFormula
M2:O35M2=LET(v,DROP(MID(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1), ind,DROP(MID(REDUCE(0,TOCOL(IFS(B2:K2<>"",B1:K1),2),LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1), s,DROP(REDUCE(0,TOCOL(B2:K2,1),LAMBDA(a,c,VSTACK(a,a+c))),1), SORT(FILTER(HSTACK(v,ind,s),s>=50),3))
Dynamic array formulas.
 
Upvote 0
I'l try to explain more.

Looking at the first few of my results, ie 931, 935, 934, 936, this is how it's derived:

Choose the largest number, 30 (index 9). Since it is not greater than 50, choose the next largest = 20 (index 3).

Both are still not greater than 50, so choose next largest = 15 (index 1). Admittedly could've chosen index 5 too.

Now all three (30 + 20 + 15) > 50, so combination = 931.

Next step, starting with 30 again and 20, choose index 5 to give 935

Next step, starting with 30 again and 20, choose index 4 to give 934

Next step, starting with 30 again and 20, choose index 6 to give 936

Now it is impossible to choose 30 and 20 and any other third number (not already chosen) to sum greater than 50, so start with 30 and choose next largest number BUT NOT index 3 (because we've already exhausted the index 9 and 3 options), so it can be index 1 or 5.

I've chosen 1 and repeated, hence next combination = 915, then 914, etc.

Hope this makes sense.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
average based on time
Replies
1
Views
373
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
213
kitm
K
J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
409
johnnyL
J
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
292
Alaa mg
A
J
  • Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

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