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

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5146
  • Total likes: 569
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #640 on: August 09, 2016, 11:55:35 AM »
Cant we just copy sheet 1 to sheet 2 and then just filter or sort column b by items above zero? What am I missing?
He could theoretically also write the order information down by hand and keep giant ledger books for everything.

In all seriousness though..
By designing and implementing formulas or macros into any workbook, you take out all the repetitive or manual tasks out of it.

Your solution has him doing 4-5 steps each time. Would be simpler to just do a pivot table each time instead of your idea, but that doesn't work for @jackofall.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #641 on: August 09, 2016, 11:56:11 AM »
Cant we just copy sheet 1 to sheet 2 and then just filter or sort column b by items above zero? What am I missing?
I assume he wants to set this up once and have non-technical users input on sheet1 and print off sheet2.

@jackofall - What's wrong with the pivot chart option? If the list of items on you 'input' sheet is static, you shouldn't have to toggle anything each time. Your only instructions to the users should be to input the 'order' on the 'input' sheet, then go to the 'output' sheet, right click on the chart and hit refresh. If that last step is too much, you can pretty easily put a button on the sheet that refreshes the pivot table. If you want it to actually print, you can easily build that in as well (potentially no need for a second sheet even).

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #642 on: August 09, 2016, 12:46:31 PM »
He could theoretically also write the order information down by hand and keep giant ledger books for everything.

In all seriousness though..
By designing and implementing formulas or macros into any workbook, you take out all the repetitive or manual tasks out of it.

Your solution has him doing 4-5 steps each time. Would be simpler to just do a pivot table each time instead of your idea, but that doesn't work for @jackofall.
+100
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #643 on: August 09, 2016, 12:49:33 PM »
I assume he wants to set this up once and have non-technical users input on sheet1 and print off sheet2.

@jackofall - What's wrong with the pivot chart option? If the list of items on you 'input' sheet is static, you shouldn't have to toggle anything each time. Your only instructions to the users should be to input the 'order' on the 'input' sheet, then go to the 'output' sheet, right click on the chart and hit refresh. If that last step is too much, you can pretty easily put a button on the sheet that refreshes the pivot table. If you want it to actually print, you can easily build that in as well (potentially no need for a second sheet even).
2 questions.
1. If the quantity changes for each order wont they have to re-select the filter options?
2. How come I dont see a filter option on my "values" column? see pic
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5146
  • Total likes: 569
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #644 on: August 09, 2016, 12:50:31 PM »
2 questions.
1. If the quantity changes for each order wont they have to re-select the filter options?
2. How come I dont see a filter option on my "values" column? see pic

You need to add the filter from the Pivot Table builder on the right side.
Then you'll filter everything except for blank and 0.

There might be a problem with this though (with the re-setting filter you mention). When you refresh manually, it won't pick up the new numbers entered, it will only activate new lines with numbers already used.

If you create a macro to run this, you can have the macro filter all numbers except for 0's and blanks, so then the pivot would work
« Last Edit: August 09, 2016, 12:56:19 PM by lubaby »

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #645 on: August 09, 2016, 01:14:58 PM »
2 questions.
1. If the quantity changes for each order wont they have to re-select the filter options?
2. How come I dont see a filter option on my "values" column? see pic

1. No
2. What lubaby said.
There might be a problem with this though (with the re-setting filter you mention). When you refresh manually, it won't pick up the new numbers entered, it will only activate new lines with numbers already used.
Not what I'm seeing. Test it out. Works for me.

Truth is, if you're interested in doing this all with a button click, you don't need the pivot table at all. Much easier to just write the 'filtering' into the macro. That's easily the cleanest way to do it.

Also, is the idea to print? Or do you need a saved sheet that has the 'filtered' version of the table?

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5146
  • Total likes: 569
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #646 on: August 09, 2016, 01:21:53 PM »
Test it out. Works for me.
Not working for me (as I wrote in my last post).


Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1995
  • Total likes: 54
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #647 on: August 09, 2016, 04:33:54 PM »

Truth is, if you're interested in doing this all with a button click, you don't need the pivot table at all. Much easier to just write the 'filtering' into the macro. That's easily the cleanest way to do it.

I was thinking the same thing. A simple macro can easily be recorded to do the following steps;
1. Copy the full order form (A:B)
2. Create a new sheet and paste the data
3. Add filter and filter all the 0's or blanks
4. Delete all those rows and unfilter
-end recording
This leaves you with just the items and qty on the order.
After recording you can add a button and assign it to this macro and it should be simple enough for any user.
I didnt get a chance to test it yet, so it may need a minor tweak, but it seems to be the simplest option to me.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #648 on: August 09, 2016, 04:41:35 PM »
I was thinking the same thing. A simple macro can easily be recorded to do the following steps;
1. Copy the full order form (A:B)
2. Create a new sheet and paste the data
3. Add filter and filter all the 0's or blanks
4. Delete all those rows and unfilter
-end recording
This leaves you with just the items and qty on the order.
After recording you can add a button and assign it to this macro and it should be simple enough for any user.
I didnt get a chance to test it yet, so it may need a minor tweak, but it seems to be the simplest option to me.
I meant to actually have the macro loop through the rows, only copying rows with >0 to the second sheet - no filter necessary. But yeah, as always there are a million ways to skin the cat in Excel.

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1995
  • Total likes: 54
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #649 on: August 09, 2016, 05:14:08 PM »
I was thinking the same thing. A simple macro can easily be recorded to do the following steps;
1. Copy the full order form (A:B)
2. Create a new sheet and paste the data
3. Add filter and filter all the 0's or blanks
4. Delete all those rows and unfilter
-end recording
This leaves you with just the items and qty on the order.
After recording you can add a button and assign it to this macro and it should be simple enough for any user.
I didnt get a chance to test it yet, so it may need a minor tweak, but it seems to be the simplest option to me.
See attached macro-enabled workbook- its nothing fancy, I just quickly put it together.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #650 on: August 09, 2016, 06:50:16 PM »
Thanks all  for the help! Much appreciated!
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5063
  • Total likes: 19
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #651 on: August 09, 2016, 10:22:22 PM »
Knew this sounded familiar. Similar problem here http://forums.dansdeals.com/index.php?topic=30173.0

Managed to dig up what I did then. Haven't looked it over and I did this right when I was learning excel so proceed at your own risk :)

See pictures below for what the 2 sheets look like. Formulas for page two (Borrowed) are:
In A3 put
Code: [Select]
=IF(COUNTIF(Input!$A:$A,SMALL(Input!$A:$A,ROW(1:1)))=0,"",VLOOKUP(SMALL(Input!$A:$A,ROW(1:1)),Input!$A:$G,COLUMN(B:B), 0))In B3
Code: [Select]
=IF(COUNTIF(Input!$A:$A,SMALL(Input!$A:$A,ROW(1:1)))=0,"",VLOOKUP(SMALL(Input!$A:$A,ROW(1:1)),Input!$A:$G,COLUMN(C:C), 0))C3 is
Code: [Select]
=IF(COUNTIF(Input!$A:$A,SMALL(Input!$A:$A,ROW(1:1)))=0,"",VLOOKUP(SMALL(Input!$A:$A,ROW(1:1)),Input!$A:$G,COLUMN(D:D), 0))and then drag them all down



« Last Edit: August 09, 2016, 10:30:26 PM by bubbles »

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #652 on: August 22, 2016, 09:47:02 PM »
I would like to learn how to set-up a macro that downloads data off a website on a specific schedule. Can someone point to a good resource so I can learn this part of Excel.

TIA.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #653 on: August 22, 2016, 10:02:01 PM »
I would like to learn how to set-up a macro that downloads data off a website on a specific schedule. Can someone point to a good resource so I can learn this part of Excel.

TIA.
This is what I would do:
Google the macro on how to download files.
Then Google how to set up a Windows scheduler to run it.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #654 on: August 22, 2016, 10:19:45 PM »
This is what I would do:
Google the macro on how to download files.
Is that specific to the website that l want to download from or it doesn't matter?
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #655 on: August 22, 2016, 10:39:06 PM »
Is that specific to the website that l want to download from or it doesn't matter?
Doesn't matter

Eg http://officetricks.com/download-file-from-website-using-excel/

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 702
  • Total likes: 34
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #656 on: August 23, 2016, 01:03:34 AM »
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #657 on: August 24, 2016, 09:55:52 PM »
Thanks. Will try it in the morning.
Depending on the data, there are much better ways. Excel has some pretty slick tools for pulling data from the web. I'll try to find more info tomorrow.

Offline MAJ

  • Dansdeals Platinum Elite
  • ****
  • Join Date: May 2012
  • Posts: 453
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: NY
Re: Excel Problem
« Reply #658 on: October 26, 2016, 01:30:04 PM »
So I had the genius idea of making a excel document with important information password protected without wiring down the password.

Naturally I forgot the password, any ways to get around this and get in the file?

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3741
  • Total likes: 27
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #659 on: October 26, 2016, 01:34:49 PM »
So I had the genius idea of making a excel document with important information password protected without wiring down the password.

Naturally I forgot the password, any ways to get around this and get in the file?
(Email it to yourself and) open in google sheets. Then save it as an excel.