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 81496 times)

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 283
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #255 on: January 31, 2015, 07:43:08 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

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 #256 on: January 31, 2015, 07:50:16 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

Do you have a master list anywhere of all customers?
If not, create one by copy pasting from all 12 months the customer account number onto one column of a new tab. Remove duplicates (under data toolbar) to have a master list of all customers billed during the year.

Then use a vlookup to pull the data from the various tabs. If each customer had several fees per month, first sort the various months and subtotal them to get the total for that customer for the month.

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 283
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #257 on: January 31, 2015, 07:58:47 PM »
Sounds good, il try that..

 Been working with array sumifs or's and's stuff but it hasnt  been working out..

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 283
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #258 on: February 01, 2015, 11:43:24 AM »
The vlookups werent working well because there were many duplicate entires and formating issues.

I ended up copying all clients from all months into column A, and all fees from all months into B. Then sorted by a/z and subtotaled.

#another twenty min job took 5.5 hours.  :-\

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9273
  • Total likes: 286
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #259 on: February 01, 2015, 11:57:32 AM »
The vlookups werent working well because there were many duplicate entires and formating issues.

I ended up copying all clients from all months into column A, and all fees from all months into B. Then sorted by a/z and subtotaled.

#another twenty min job took 5.5 hours.  :-\
OT for this thread, but it sounds like your situation might work better in Microsoft Access.
Workflowy. You won't know what you're missing until you try it.

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2755
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #260 on: February 01, 2015, 01:53:06 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

By far, the easiest way to do this is a pivot table.
  • Open your spreadsheet and hit ALT>D>P to start the pivot table wizard.
  • On the first page of the wizard, select 'Multiple consolidation ranges' and click next.
  • On the next screen, select 'I will create the page fields' and click next.
  • On the next screen, press the little icon with the red arrow, navigate to your first monthly tab, and select the whole table that has your values, including the headers, names, and fee values. If you want to set this up before the values are put in (ie at the beginning of the year), just highlight the entire columns - for example Sheet1!$A:$B if you have only 2 columns of data - and that way later you can just refresh the pivot table results after new data has been entered. Once you have the range selected, click add, and repeat the process for each monthly tab, then click next once you finish the last one.
  • On the next screen, select 'New worksheet' and click finish.
The wizard will then generate a pivot table, and a pivot report showing the results. Each row in the pivot table will show one customer name, alphabetized, with no duplicates. When the report is first generated, it'll show a count of how many values there are for each name. To switch that to a sum of actual values, look at the bottom right corner of your screen, in the Values section of the PivotTable Field List window. Click the little arrow next to the Count of Value field, select Value Field Settings, and switch from Count to Sum in the window that pops up. The report will now show the final result.

If you add or remove data from the source ranges (ie your monthly tabs) later, you can always click into any cell in the report and hit ALT>J>T>F>A to refresh the pivot table.

ETA: I did that in Excel 2007, but it should work similarly in all newer versions as well at the very least.
« Last Edit: February 01, 2015, 01:58:04 PM by skyguy918 »

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 283
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #261 on: February 01, 2015, 02:54:27 PM »
Thanks,
I tried to experiment with the pivot table with excel 2013, seems very complicated... Asking me about related table ranges etc. -Either way, I keep  getting an error that excel cant do it with the available resources and im just playing with jan and feb!

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2755
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #262 on: February 01, 2015, 10:59:12 PM »
Thanks,
I tried to experiment with the pivot table with excel 2013, seems very complicated... Asking me about related table ranges etc. -Either way, I keep  getting an error that excel cant do it with the available resources and im just playing with jan and feb!
Was that even with the steps I mentioned above, or does 2013 not follow that exact script?

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 283
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #263 on: February 01, 2015, 11:35:47 PM »
Was that even with the steps I mentioned above, or does 2013 not follow that exact script?
To me it looked like 2013 is totally different. Definitely couldn't follow the step by step..
Needed to set data as tables first in order to use multiple ranges etc

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2755
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #264 on: February 02, 2015, 10:00:56 AM »
To me it looked like 2013 is totally different. Definitely couldn't follow the step by step..
Needed to set data as tables first in order to use multiple ranges etc
Does ALT>D>P get you to the wizard? I googled it and it sounds like 2013 has the exact same wizard as 2007.

Here's a tutorial made for Excel 2013 that has screenshots for everything I mentioned. The relevant details are on pages 1 and 2:
http://prairiestate.edu/Assets/Global/itr-department/office/2013/Excel13-PivotTables_MultipleSheets.pdf

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6847
  • Total likes: 6
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • Flight Lynx
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel Problem
« Reply #265 on: February 24, 2015, 01:21:24 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
You may not hold me responsible or liable for any actions taken that was recommended from my account or username.

Online lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4391
  • Total likes: 194
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #266 on: February 24, 2015, 01:47:02 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
Pivot Table wouldn't do it for you?
Once the game is over, the king and the pawn go back in the same box.

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2755
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #267 on: February 24, 2015, 02:01:34 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
Are you asking how to set this up in Excel, or how to have HTML code that looks in the Excel file for the counties/airports?

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6847
  • Total likes: 6
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • Flight Lynx
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel Problem
« Reply #268 on: February 24, 2015, 02:36:28 PM »
Are you asking how to set this up in Excel, or how to have HTML code that looks in the Excel file for the counties/airports?

Looking how to set it up in html code so i won't have to retype all the info.

But a html code that when they start typing it would look up the text in a excel file would be pretty cool
You may not hold me responsible or liable for any actions taken that was recommended from my account or username.

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9273
  • Total likes: 286
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #269 on: February 24, 2015, 02:37:14 PM »
Looking how to set it up in html code so i won't have to retype all the info.

But a html code that when they start typing it would look up the text in a excel file would be pretty cool
Is this form going to be on a website?
Workflowy. You won't know what you're missing until you try it.