Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 81770 times)

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #165 on: July 11, 2014, 01:17:43 PM »
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.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2756
  • Total likes: 129
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #166 on: July 11, 2014, 01:19:47 PM »
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.

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #167 on: July 11, 2014, 01:24:10 PM »
40 small to 8 totals per month. for a year

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2756
  • Total likes: 129
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #168 on: July 11, 2014, 01:30:05 PM »
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?

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #169 on: July 11, 2014, 01:45:54 PM »
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.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7116
  • Total likes: 253
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #170 on: July 11, 2014, 01:53:45 PM »
can a subtotal + if formula work?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2756
  • Total likes: 129
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #171 on: July 11, 2014, 03:46:04 PM »
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.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2756
  • Total likes: 129
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #172 on: July 11, 2014, 03:46:27 PM »
can a subtotal + if formula work?
Only if the donations are listed in chronological order.

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4394
  • Total likes: 1
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #173 on: July 11, 2014, 05:47:39 PM »
You should check out the "solver" excel add in

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3706
  • Total likes: 11
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #174 on: July 11, 2014, 06:00:45 PM »

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2756
  • Total likes: 129
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #175 on: July 12, 2014, 10:29:39 PM »
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?

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3706
  • Total likes: 11
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #176 on: July 12, 2014, 10:32:42 PM »
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

Offline beej

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jun 2010
  • Posts: 410
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Israel
  • Programs: SPG Gold
Re: Excel Problem
« Reply #177 on: July 13, 2014, 12:39:30 AM »
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.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7116
  • Total likes: 253
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #178 on: July 13, 2014, 12:47:42 AM »
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?

Offline beej

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jun 2010
  • Posts: 410
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Israel
  • Programs: SPG Gold
Re: Excel Problem
« Reply #179 on: July 13, 2014, 01:00:59 AM »
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.