Combination Function

Please see attached excel sheet.

(If for some reason attached excel sheet doesnt work, here is a quick explanation)

Basically, this is a very SMALL example of what i need to do.

Each row in column A is a sum of multiple rows in column B.

Is there a function i can put in somewhere that will find combinations of numbers in column B that equal different rows in column A.

If that doesnt quite make sense please reply and let me know.

And if you have a solution, that will be AMAZING




Very simple example below:

Row 1 Column A: 10
2A: 20
3A: 50

Row 1 Column B: 4
2B: 6
3B: 17
4B: 3
5B: 25
6B: 25

For intsance, 1B and 2B = 1A
3B and 4B = 2A
and 5B and 6B = 3A

my excel sheet wont be near as this simple, but thats what im trying to do. Find a funciton that will determine the combinations in Column B to equal each line, or some of the lines in Column A. It wont be a perfect match either, but i at least want to get as many as i can.

Thanks

I am not exactly sure I follow, your spreadsheet doesn't seem to sum up the way your sample does....

To sum every 2 cells in column B, use:

=SUM(INDEX($B$2:$B$60,1+((ROW(A1)-1)*2)):INDEX($B$2:$B$60,2+((ROW(A1)-1)*2)))

and copy down... if that is what you want...

adjust ranges to suit.

the two columns in the real spreadsheet im working on will not sum up

this is because of monthly timing differences.

for instance, the amount of 100.00 might appear in column A (at end of the month), but it wont appear in column B for that month because of timing differences (it will appear on the next month for column b)

so yes, there will be amounts that are leftover mostly towards the top and towards the bottom, which i will then carryforward any that aren't summed to the next month

im trying to tie out incredibly difficult bank recons from the bank statements to our daily cash statements

it wont always be every two cells either.

it might be that A1 = B3, B6, B7, and B9
or it might be A1 = B1

the number in A1 can be the total of one row in column B or all the rows in column B, or anywhere in between.
then A2 will be derived the same way,

sorry, i should have explained that part

my example i gave should have said this instead:
Row 1 Column A: 2
2A: 20
3A: 50

Row 1 Column B: 4
2B: 6
3B: 17
4B: 3
5B: 25
6B: 25

For intsance
3B and 4B = 2A
and 5B and 6B = 3A
and 1A will not tie out to 1B and/or 2B so i will just carry those amounts to the next month's spreadsheet and see if a timing difference catches those.

its basically a giant math puzzle, and i dont have time to sit here and pick and guess which numbers total this or that

does my explanation after your reply help clear up what im trying to say?

the easiest way to say it is..

somewhere in column B, a certain combination of amounts equal A1

then, somewhere in column B, a certain combination of amounts equal A2

and so on, and so on

i need to know those combinations, whether its two amounts equal a row in column A, or whether its 10 amounts that equal a row in column A

this forum hasn't let me down yet. you guys always figure them out!

if you can give me a function for this one i'll set this website as my homepage on my

Well i am very confused... but that might just be me not picking up some clues...

I suggest you repost your sample and show what are you original numbers and what are you expected results.. and explain how they are gotten...

Don't leave out import information/patterns etc... your sample just shows 2 simple columns and I don't know if that is what you actually have....

Note: not asking for any confidential info, just a better representation of what you have.

I have to leave to pick up my son... so not sure if I will get back to this this weekend...

.. if anyone else has ideas or wants to continue, please do.

ok, i went back and edited my original post, and i added another spreadsheet, this one called TEST1111.xls

if you have the time (and patience with me), please open that and read below

notice in bold in column A, the amount 282.69

then notice in bold in column B, the amounts
97.47
92.43
3.92
5.94
70
12.93

the sum of those bolded amounts in column B equal the amount of 282.69 in A4

i got lucky and found that one

however there are too many combinations in column b that equal different rows in column a that take way too much time trying to find

i need a formula for instance i can put in C4, that will in some form or fashion show me which amounts in column B equal that amount in column A4

then the same for C5 (which amounts in column B equal A5), then C6 (which amounts in column B equal A6)

Have a look through this article and the samples...

http://www.tushar-mehta.com/excel/te...ues/index.html