0

I have sheet A with two columns: Name & Amount. In sheet B I have a list of totals amounts. Each total in sheet B is the sum of some combination of amounts in Sheet A. I routinely have to take a total amount in Sheet B and find the amounts (and names) from Sheet A that make up that total. My process now involves filtering the Amount column in Sheet A to amounts less than the total in Sheet B and summing various combinations until I find the right one. This is very tedious and time consuming.

Is there a better way to do this?

  • It's certainly programmatically possible, but it sounds like there's a business problem if a manager thinks this is a good use of company time. – Christopher Hostage Aug 25 '17 at 20:01
  • On sheet B, are the totals by name? If this is a routine requirement, why not save the details composing the sums? Automate that and you never need to backtrack. – fixer1234 Aug 25 '17 at 20:03
  • @fixer1234 totals are by date. The majority - 95% - of the totals in Sheet B already have breakdowns, and the breakdowns are usually the sum of amounts by name and date in Sheet A. However, these two sheets are not directly connected. There are a few totals that are the sum of multiple amounts from multiple people on multiple dates. That's what I'm concerned about. – Ron L Aug 25 '17 at 20:16
  • 1
    You can automate the brute-force search with VBA. But this is a well-known problem, and there is no easy answer. – Scott Aug 25 '17 at 21:21
  • I agree with @ChristopherHostage. This should be tackled by fixing the process that even comes up with the totals that nobody can interpret. – teylyn Aug 25 '17 at 21:28
0

Assuming the combinations are of two amounts only from SheetA, there is a fairly simple way of doing this.

Create a new Sheet - SheetC. In Column A, paste the "Amount" column from SheetA, e.g. from A2:A50 (depending on how long the list is).

Use Paste Special - Transpose to paste the "Amount" Column again in SheetC, but transposing it so that the column turns into a row, e.g. from B1:AX1.

You will now have all your amounts in SheetC twice: once in Column A, and again in Row 1.

You can now easily use all the cells in the table to add every combination. I.e.enter in cell B2 the formula =$A2+C$1, then copy this to every cell in the range, e.g. B2:AX50.

Once this is done you can go into SheetB where the combinations are, copy an amount to clipboard. Then go back into SheetC, and Find (Ctrl+F), inputting that amount from clipboard. Excel will find it for you somewhere in the table, and you can then see the amounts its made of and easily work out which amounts in SheetA these are.

You can then refine this method by using MATCH() in SheetB to automate finding the value in SheetC which corresponds to each value in SheetB. Using the returned number you can get excel to get you the names of each of the constituent amounts with the INDEX() formula.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.