Calculate multiples and combinations for a set of numbers up to max of 5x the greatest number

E

epete

New Member
Joined
Jan 4, 2014
Messages
14
Hello all,

I could not find anything out there that quite did what I need to accomplish. Hope someone can point me in the right direction at least.

Need to take a .csv file with rows containing 3 data items such as below:
header1, header2, header3
A1234, 8021, 5
A1234, 8021, 7
A1234, 8021, 10
A1234, 8021, 12
A1234, 8021, 18
A1234, 8021, 20
A1234, 8021, 150
A1234, 8021, 500
A1234, 8065, 5
A1234, 8065, 32
A1234, 8065, 150​

The second data item in each row will differ for the sets of numbers. For each set of numbers I need to cycle through and remove any values that are multiples of lower numbers. (ie. I would remove 10, 20, 150 from the first set). Need to keep the highest number. Also need to remove amounts that can be calculated by combinations of lower numbers. (ie. 12 because it can be derived from 5 + 7). So what is left for the first set is 5,7,18,500. Need that done for each set of numbers.

Then need to calculate the max amount which would be the highest number x 5 for each set. (ie. first set max would be 500 x 5 = 2500. Second set would be 150 x 5 = 750).

Now for each set of numbers need to calculate all of the multiples and combinations of the numbers in the set up to the max amount. (ie. first set would start off with 5,7,10,12,14,15,19,20,21,etc). Needs to be done for each set.

Will need to remove any duplicates from the output.

Will need to write out the data formatted something like the following:
000008021A1234000000500
000008021A1234000000700
000008021A1234000001000
000008021A1234000001200
000008021A1234000001400
etc
​

Any help is greatly appreciated! Thanks!
 

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.
Sort by date Sort by votes
I would still love to hear if anyone has any ideas on how this could be done. I'd even take it if it was done with .xls/.xlsm instead of .csv file. Thanks!
 
Upvote 0
I'm having a hard time understanding what constitutes a 'set'. Please clarify this. Are only the numbers under header 3 the numbers we are dealing with?
 
Upvote 0
Also, what do you mean by 'lower numbers'? Do you mean numbers underneath or numbers lower in value? Either way, I'm confused as to why the 500 was no removed from the final list above, as it is a multiple of 5. Is it because it is the highest number in value?
 
Upvote 0
pleeseemailme said:
I'm having a hard time understanding what constitutes a 'set'. Please clarify this. Are only the numbers under header 3 the numbers we are dealing with?
Click to expand...

A set of numbers is based off the second data element. Each set of numbers will have the same value for the second data element. All the values in the third data element that have matching numbers in the second data element will make up a set.
 
Upvote 0
pleeseemailme said:
Also, what do you mean by 'lower numbers'? Do you mean numbers underneath or numbers lower in value? Either way, I'm confused as to why the 500 was no removed from the final list above, as it is a multiple of 5. Is it because it is the highest number in value?
Click to expand...

Yes, I am referring to the lower value numers of the set. The 500 was not removed because it was the largest number (which should get mutiplied by 5 to compute the max amount for which combinations and multiples will be computed up to).

Thanks for the response!
 
Upvote 0
epete said:
Hello all,

I could not find anything out there that quite did what I need to accomplish. Hope someone can point me in the right direction at least.

Need to take a .csv file with rows containing 3 data items such as below:
header1, header2, header3
A1234, 8021, 5
A1234, 8021, 7
A1234, 8021, 10
A1234, 8021, 12
A1234, 8021, 18
A1234, 8021, 20
A1234, 8021, 150
A1234, 8021, 500
A1234, 8065, 5
A1234, 8065, 32
A1234, 8065, 150​

The second data item in each row will differ for the sets of numbers. For each set of numbers I need to cycle through and remove any values that are multiples of lower numbers. (ie. I would remove 10, 20, 150 from the first set). Need to keep the highest number. Also need to remove amounts that can be calculated by combinations of lower numbers. (ie. 12 because it can be derived from 5 + 7). So what is left for the first set is 5,7,18,500. Need that done for each set of numbers.

Epete, assuming that 22 was a number it the first set, would it also be removed because it is the sum of 5, 7, and 10? In other words, any number that is a combination of the sum of any amount of lower numbers needs to be removed?

Then need to calculate the max amount which would be the highest number x 5 for each set. (ie. first set max would be 500 x 5 = 2500. Second set would be 150 x 5 = 750).

Now for each set of numbers need to calculate all of the multiples and combinations of the numbers in the set up to the max amount. (ie. first set would start off with 5,7,10,12,14,15,19,20,21,etc). Needs to be done for each set

Why in this list above is 17 not present as it is the sum of 7 and 10? Would 22 be in the list as the sum of 5, 7, and 10?

Will need to remove any duplicates from the output.

Will need to write out the data formatted something like the following:
000008021A1234000000500
000008021A1234000000700
000008021A1234000001000
000008021A1234000001200
000008021A1234000001400
etc
​

Any help is greatly appreciated! Thanks!
Click to expand...

Please see requests for further clarification above in red.
 
Upvote 0
Hi,

Yes, if 22 was a number it should be removed as it can be derived by combination of the lower value numbers. Any value that can be derived from combination of the lower values should be removed.

Sorry, I just overlooked putting 17 in the list. You are correct, it should be there.

Thanks!
 
Upvote 0
epete,
A couple of more things: In the list where 17 should have been included, will 35 also be included because it is a multiple of 5 and 7?

Also, does your spreadsheet have more than just the 3 columns of data you've shown? I ask because I've solved the first question of removing multiples, but it requires many columns beyond the 3. Also, my solution won't allow numbers greater than 16,384 to be evaluated. Will you ever have a number this big?

Also, how large can some of these sets get? Meaning, how many numbers, or rows, could be in a set?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Lottery Combinations Filter
Replies
2
Views
538
Fluff
Fluff
T
  • Solved
Weighted lottery help?
2
Replies
14
Views
2K
bebo021999
B
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
395
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
C
  • Solved
Help with max rows of a million+
14 15 16
Replies
151
Views
6K
johnnyL
J

Forum statistics

Threads
1,214,833
Messages
6,121,868
Members
449,053
Latest member
Mesh

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