Most valuable professional
  • Hot Topics

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

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

This question generated 25 answers. To proceed to the answers, click here.

This thread is current as of January 4, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel