Excluding used numbers from formulas


Well-known Member
Jul 8, 2003
I am trying to figure out a way to get the greatest number of combinations without reusing a number. For example if I have a column with these numbers:


and I want to show all the combinations that equal 48 I would like to see 24 & 24, 16 & 32 matched together. However I would want the 13 and the third 24 to be excluded. The thirteen is not that difficult, but the third 24 is where I am having difficulty. These numbers are quantities of a particular product, so I would need for the combinations to apply to a particular product. My list contains many products with varying quantities. The 48 in my example is the ammount of product on a full pallet. I would like the formula to take the full pallet ammount, and subtract say the first 24. Then it would look down the list to match the difference. Where it should find the second 24. After that match is made the third 24 will not use the first two 24s as its match, but will instead look for another match. In the next column there is a PO# that I would like returned as the match.

To give you an idea of what I wish to accomplish.... We receive orders that list how many of a product the customer wishes to order on a PO (Purchase Order). My guys will then take their tickets and pick the loads. If one of my guys needs 24 cases of a product from a pallet with 48. I would like to save the next person with a ticket for 24 cases of the same product from having to lift those cases. Instead the first person could set it to the side for the second person. In order to do this I would need to know which POs I can mate with one another. My guys work hard, and I would like to save them from needless work. You will be helping several people if you tackle this one.

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics