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

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 675
  • Total likes: 23
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #630 on: August 08, 2016, 09:15:47 PM »
Pivot tables can have a filter to only show lines with quantity higher then 0.
I don't want to keep on toggling the filter. Any other options.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4752
  • Total likes: 330
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #631 on: August 08, 2016, 09:48:45 PM »
I don't want to keep on toggling the filter. Any other options.
Certainly.

With Excel, there's always more options. Just depends how creative you can get..

This should do it for you as a first draft.
A) Make a new sheet.
B) Copy the list of items from Sheet1 Column A to Sheet2 Column A.
C) Highlight the new Column A, then from the top bar, click Data, then Remove Duplicates.
You should now have a list of all the items from Sheet1 with only 1 rendition of each line item.

D) Now, in Sheet2 Column B1, enter the following formula. Adjust line number "A1" if there's headers. Change "Sheet1" to whatever your datasheet is called:
Code: [Select]
=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)
If you'll be doing this a lot, we can formula-ize the first 3 steps (or even write a Macro to do the whole thing for you with just a keyboard shortcut).
« Last Edit: August 08, 2016, 09:56:34 PM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 675
  • Total likes: 23
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #632 on: August 08, 2016, 11:18:23 PM »
Certainly.

With Excel, there's always more options. Just depends how creative you can get..

This should do it for you as a first draft.
A) Make a new sheet.
B) Copy the list of items from Sheet1 Column A to Sheet2 Column A.
C) Highlight the new Column A, then from the top bar, click Data, then Remove Duplicates.
You should now have a list of all the items from Sheet1 with only 1 rendition of each line item.

D) Now, in Sheet2 Column B1, enter the following formula. Adjust line number "A1" if there's headers. Change "Sheet1" to whatever your datasheet is called:
Code: [Select]
=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)
If you'll be doing this a lot, we can formula-ize the first 3 steps (or even write a Macro to do the whole thing for you with just a keyboard shortcut).
Thanks. But I need something a little mores simple. As this will be used over and over again with non excel experts.
What I need, I would imagine, is pretty simple, yet I can figure out an easy solution.
What I have is an order sheet with lots of items. Each workbook is a unique order. I would like to summarize the order in a new sheet and only pull the items that are relevant to this order and not the whole list of all available items.
......
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4752
  • Total likes: 330
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #633 on: August 09, 2016, 12:03:09 AM »
Thanks. But I need something a little mores simple. As this will be used over and over again with non excel experts.
What I need, I would imagine, is pretty simple, yet I can figure out an easy solution.
What I have is an order sheet with lots of items. Each workbook is a unique order. I would like to summarize the order in a new sheet and only pull the items that are relevant to this order and not the whole list of all available items.
......
So something like this?

All items:


Only items on order:
Once the game is over, the king and the pawn go back in the same box.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 675
  • Total likes: 23
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #634 on: August 09, 2016, 12:24:51 AM »
So something like this?

All items:


Only items on order:

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

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1927
  • Total likes: 26
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #635 on: August 09, 2016, 09:16:17 AM »
Yes!!
Will the order sheet ever have an item listed more than once?
Will the order sheet always list all items and its just the qty that changes based on the order?

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 675
  • Total likes: 23
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #636 on: August 09, 2016, 10:24:11 AM »
Will the order sheet ever have an item listed more than once?
Will the order sheet always list all items and its just the qty that changes based on the order?
No
Correct. And most items will be 0.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4752
  • Total likes: 330
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #637 on: August 09, 2016, 10:47:32 AM »
Yes!!
So I have a working Sample Workbook for you.

Now we just need to figure out the simplest way for you to implement it.

Try this:
A) I'm assuming your worksheets have headers, so you need to adjust the formulas if there aren't any. If your worksheets have names other then the standard Sheet1, Sheet2, Sheet3 change it now to Sheet1, Sheet2, Sheet3 so you don't need to update the formulas. You can change the worksheet names after and it will auto update the formulas.

B) Create a Sheet2. In Sheet2 field A2 paste the following, then drag it down as to as many lines as Sheet1 has.
Code: [Select]
=IF(Sheet1!B2>0,Sheet1!A2,"")this is pulling the data lines from Sheet1 that have quantity on order greater then 0
You can ignore this sheet now. I'm just using it for the logic so your other worksheets look clean.

C) Create a Sheet3. Paste the following into Sheet3 field A2. Then click into the text and do CTRL+SHIFT+ENTER to make it an Array Formula.
Then, drag it down to as many lines as is in Sheet1. (The formula is counting up to 5001 lines, so change the number to higher if needed).
Code: [Select]
=(IFERROR(INDEX(Sheet2!A$2:A$5001,SMALL(IF(Sheet2!A$2:A$5001<>"",ROW(Sheet2!A$2:A$5001)-ROW(Sheet2!A$2)+1),ROWS(Sheet3!A$2:A2))),""))

E) In Sheet3 field B2, paste the following.
Code: [Select]
=IFERROR(INDEX(Sheet1!$B$2:$B$5001,MATCH($A2,Sheet1!$A$2:$A$5001)),"")
Let me know if there are any problems or changes you want to see.
« Last Edit: August 09, 2016, 11:07:00 AM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 675
  • Total likes: 23
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #638 on: August 09, 2016, 11:23:35 AM »
So I have a working Sample Workbook for you.

Now we just need to figure out the simplest way for you to implement it.

Try this:
A) I'm assuming your worksheets have headers, so you need to adjust the formulas if there aren't any. If your worksheets have names other then the standard Sheet1, Sheet2, Sheet3 change it now to Sheet1, Sheet2, Sheet3 so you don't need to update the formulas. You can change the worksheet names after and it will auto update the formulas.

B) Create a Sheet2. In Sheet2 field A2 paste the following, then drag it down as to as many lines as Sheet1 has.
Code: [Select]
=IF(Sheet1!B2>0,Sheet1!A2,"")this is pulling the data lines from Sheet1 that have quantity on order greater then 0
You can ignore this sheet now. I'm just using it for the logic so your other worksheets look clean.

C) Create a Sheet3. Paste the following into Sheet3 field A2. Then click into the text and do CTRL+SHIFT+ENTER to make it an Array Formula.
Then, drag it down to as many lines as is in Sheet1. (The formula is counting up to 5001 lines, so change the number to higher if needed).
Code: [Select]
=(IFERROR(INDEX(Sheet2!A$2:A$5001,SMALL(IF(Sheet2!A$2:A$5001<>"",ROW(Sheet2!A$2:A$5001)-ROW(Sheet2!A$2)+1),ROWS(Sheet3!A$2:A2))),""))

E) In Sheet3 field B2, paste the following.
Code: [Select]
=IFERROR(INDEX(Sheet1!$B$2:$B$5001,MATCH($A2,Sheet1!$A$2:$A$5001)),"")
Let me know if there are any problems or changes you want to see.
I will try it and let you know!
Dude you are amazing! You took the time and effort to help a stranger......
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: 7247
  • Total likes: 282
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #639 on: August 09, 2016, 11:46:00 AM »
So something like this?

All items:


Only items on order:

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?

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4752
  • Total likes: 330
  • 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.
Once the game is over, the king and the pawn go back in the same box.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3080
  • Total likes: 234
  • 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: 675
  • Total likes: 23
  • 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: 675
  • Total likes: 23
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4752
  • Total likes: 330
  • 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 »
Once the game is over, the king and the pawn go back in the same box.