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

Offline Work-for-ur-muny

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2014
  • Posts: 3101
  • Total likes: 315
  • DansDeals.com Hat Tips 34
    • View Profile
Re: Excel Problem
« Reply #620 on: March 15, 2016, 04:25:30 PM »
For date (where col C is the date they entered)

Code: [Select]
=COUNTIFS($A$2:$A$1000,A3,$B$2:$B$1000,B3,$C$2:$C$1000,">="&TODAY()-14)
Just remember that this only works if you're always using the current date as the endpoint of your range (ie and not an earlier date). You could make this setup variable by replacing that 14 with a fixed reference to a cell somewhere on the sheet that you can enter any number of days in.
Basically, when a customer walks in I want to see how many times he entered in the last two weeks.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #621 on: March 15, 2016, 04:33:16 PM »
Basically, when a customer walks in I want to see how many times he entered in the last two weeks.

I it's always a set amount of days then mine will work fine, otherwise (as skyguy said) you can reference a cell and enter the amount of previous days there.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #622 on: March 15, 2016, 05:30:48 PM »
I have office 2016. I cant open any Excel sheet that is saved as the older version unless I open a blank Excel look for the file and open. This is really a pain in the neck. Anyway to change this so I can open these files just by clicking on them?
TIA
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 895
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #623 on: April 15, 2016, 04:34:32 AM »
What's wrong with this? It keeps giving me an N/A or error.
=sumif(A:A, "MR", (B:B-C:C))
I don't sin, I give myself opportunities to repent.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5125
  • Total likes: 558
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #624 on: April 15, 2016, 10:24:52 AM »
What's wrong with this? It keeps giving me an N/A or error.
=sumif(A:A, "MR", (B:B-C:C))
Replace it with this to start. Will try to simplify it if I have more time.

=SUMIF(A:A,"MR",B:B)-SUMIF(A:A,"MR",C:C)

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 895
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #625 on: April 15, 2016, 10:58:09 AM »
Replace it with this to start. Will try to simplify it if I have more time.

=SUMIF(A:A,"MR",B:B)-SUMIF(A:A,"MR",C:C)
Thank you.
I don't sin, I give myself opportunities to repent.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 287
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #626 on: June 10, 2016, 10:24:51 AM »
How do I extract a bunch of PDFs that are embedded within a bunch of excel files?
Even drag and drop not working..

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12454
  • Total likes: 7085
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #627 on: June 10, 2016, 10:44:07 AM »
How do I extract a bunch of PDFs that are embedded within a bunch of excel files?
Even drag and drop not working..
http://www.howtogeek.com/50628/easily-extract-images-text-and-embedded-files-from-an-office-2007-document/
Feelings don't care about your facts

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #628 on: August 08, 2016, 07:55:37 PM »
I have data sheet as follows.
Colum a has lots of different items. Column b has the quantity ordered. I would like to summarize the order on second tab. What is the best way to pull this data into tab 2.
My first thought is a simple pivot table. My question is how do I pivot the data so only items that have a quantity show up in the pivot table?
PS also open to better ideas.
TIA
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: 5125
  • Total likes: 558
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #629 on: August 08, 2016, 09:12:10 PM »
I have data sheet as follows.
Colum a has lots of different items. Column b has the quantity ordered. I would like to summarize the order on second tab. What is the best way to pull this data into tab 2.
My first thought is a simple pivot table. My question is how do I pivot the data so only items that have a quantity show up in the pivot table?
PS also open to better ideas.
TIA
Pivot tables can have a filter to only show lines with quantity higher then 0.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5125
  • Total likes: 558
  • 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 »

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5125
  • Total likes: 558
  • 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:

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • 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: 1990
  • Total likes: 52
  • 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: 696
  • Total likes: 31
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5125
  • Total likes: 558
  • 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 »

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2012
  • Posts: 696
  • Total likes: 31
  • 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: 7273
  • Total likes: 287
  • 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?