Excel Help and Problems

[etech0]

do i have to write the program, there is nothing set up for this yet?

right, unless you happen to find something by googling. I might be able to help with this next week, but you might not need something so complicated.

[unavailable]

right, unless you happen to find something by googling. I might be able to help with this next week, but you might not need something so complicated.

i'll try

[skyguy918]

i'll try

If the list is small you can probably do it manually by yourself pretty easily. The larger the list is the more likely you need a VBA script to essentially compare the sum of each combination of the small amounts to the daily totals until every small amount is matched to a daily total.

[unavailable]

If the list is small you can probably do it manually by yourself pretty easily. The larger the list is the more likely you need a VBA script to essentially compare the sum of each combination of the small amounts to the daily totals until every small amount is matched to a daily total.

about 40;8 (x12 months), but also there might be some missing so if there is a program or feature that can do this I'll prefer not to do it manually just to find out that something's missing

haven't applied this yet but it looks promising. Ill let you know

[skyguy918]

about 40;8 (x12 months), but also there might be some missing so if there is a program or feature that can do this I'll prefer not to do it manually just to find out that something's missing

haven't applied this yet but it looks promising. Ill let you know

I took out my edit. It'll only help you match a maximum of 200 small amount to 1 daily total at a time.

[unavailable]

I took out my edit. It'll only help you match a maximum of 200 small amount to 1 daily total at a time.

so i can still leave column a as is and place 1 daily total at a time. beats manual labor.

[skyguy918]

about 40;8 (x12 months), but also there might be some missing so if there is a program or feature that can do this I'll prefer not to do it manually just to find out that something's missing

haven't applied this yet but it looks promising. Ill let you know

I can't tell what you're trying to say. What is the total number of all the 'small transactions' you have in your data set?

so i can still leave column a as is and place 1 daily total at a time. beats manual labor.

Yes this definitely will help you somewhat. You'd have to solve the first daily amount, move the transactions that remain with a 1 marker out of the transactions list, and then repeat that process for each daily amount. VBA could automate that part as well.

[unavailable]

40 small to 8 totals per month. for a year

[skyguy918]

40 small to 8 totals per month. for a year

so you have 12 groups of 40 transactions, and each group of 40 transactions must be matched up to 1 of 8 totals within each month?

[unavailable]

so you have 12 groups of 40 transactions, and each group of 40 transactions must be matched up to 1 of 8 totals within each month?

40 donations in month #1.
8 deposits (containing multiple donations) in month #1. Want to know which donations were grouped to create each of the 8 deposits.
 This has been going on for a year.

[churnbabychurn]

can a subtotal + if formula work?

[skyguy918]

40 donations in month #1.
8 deposits (containing multiple donations) in month #1. Want to know which donations were grouped to create each of the 8 deposits.
 This has been going on for a year.

Ok, so:
Create 1 tab per month.

• On each tab, have the donations for that month listed down the first column starting from cell A2, and the deposits for that month listed across the first row starting from cell E1.
• Fill cells B2:I41 (assuming your data is exactly 40x8) with the value 1.
• In cell B42, put "=SUMPRODUCT($A$2:$A$41,B2:B41)-B1" (without the quotes obviously).
• Copy that cell B42 to cells C42:I42.

Now you can do the solver method on each cell within B42:I42. You still have to do the solver 8 time per tab X 12 tabs = 96 times, but as has been mentioned already, VBA is the way to solve that.

[skyguy918]

can a subtotal + if formula work?

Only if the donations are listed in chronological order.

[smurf]

You should check out the "solver" excel add in

[MarkS]

Also see ASAP Utilities add on

http://www.asap-utilities.com/download-asap-utilities.php

[skyguy918]

You should check out the "solver" excel add in

That's what I had added to my earlier post, a link to an explanation of how to use solver for this exact problem. I removed it because I thought the solver limitation of 100 variable would be a problem in this case - turns out it's not.

Also see ASAP Utilities add on

http://www.asap-utilities.com/download-asap-utilities.php

Do you actually know of a tool in there that would solve this specific problem?

[MarkS]

Do you actually know of a tool in there that would solve this specific problem?

No - I was just posting it as an overall useful Excel Add on

[beej]

If I have spreadsheet with 2 columns. Each colum containing a list of names. Lest say column A has Bob, Joe, and Steve in that order. And column B has Joe Steve and  Bob int hat order.
How would I filter out all the records that have Bob at once.
If I filter column A for Bob I right away lose the next two rows so I can't further filter column B for Bob too.
Basically is there a way to have multiple column filters work as an AND, meaning together with the previous filters.

[churnbabychurn]

If I have spreadsheet with 2 columns. Each colum containing a list of names. Lest say column A has Bob, Joe, and Steve in that order. And column B has Joe Steve and  Bob int hat order.
How would I filter out all the records that have Bob at once.
If I filter column A for Bob I right away lose the next two rows so I can't further filter column B for Bob too.
Basically is there a way to have multiple column filters work as an AND, meaning together with the previous filters.

But why can't you do one at a time?

[beej]

I simplified the scenario. I have  list of hundreds of transactions. I also have about 10 columns of the different people involved in that transaction. I need to see all the transactions Bob was involved with. He might be in the first column on one transaction, the third on another and the 10th column on another. It would be time consuming to do it separately.