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

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 431
  • Total likes: 38
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #680 on: December 09, 2016, 10:52:17 AM »
how do I fix my date format within a pivot table to short date??
In the pivot table set up on the right, click on the field that you want to change, in this case date.

Choose the last option, field settings.

On the left of the "OK" sign, click "Number Format"

Then choose from the categories and the options to the format that you want.

Hope that helps.

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 #681 on: December 09, 2016, 02:08:37 PM »


In the pivot table set up on the right, click on the field that you want to change, in this case date.

Choose the last option, field settings.

On the left of the "OK" sign, click "Number Format"

Then choose from the categories and the options to the format that you want.

Hope that helps.

It doesn't help..

It doesn't have number format for date

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Problem
« Reply #682 on: December 10, 2016, 09:27:05 PM »
looking for a spreadsheet - or any for of figuring out commissions.
I'm willing to pay ( please pm me)
Would like to enter type of sale as commissions are different and each level has different threshold minimums.
I would like to enter the name and amount of sales in each bracket and on the right side should come all the totals
on the bottom a grand total yo be paid .
you can only make a first impression ONCE

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Problem
« Reply #683 on: December 10, 2016, 09:28:31 PM »
2nd post.
looking for someone to teach / explain the use of pivot tables and v-lookup. willing to pay
(pm only)
Thank you
you can only make a first impression ONCE

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3742
  • Total likes: 28
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #684 on: December 10, 2016, 09:30:54 PM »
2nd post.
looking for someone to teach / explain the use of pivot tables and v-lookup. willing to pay
(pm only)
Thank you
Google it. You can learn it easily yourself

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Problem
« Reply #685 on: December 10, 2016, 09:35:39 PM »
Google it. You can learn it easily yourself
looking for next level
(lynda)
you can only make a first impression ONCE

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12706
  • Total likes: 7378
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #686 on: December 10, 2016, 09:56:54 PM »
looking for next level
(lynda)
there are many next level courses in places like udemy
Feelings don't care about your facts

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1808
  • Total likes: 232
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #687 on: December 10, 2016, 11:28:59 PM »
looking for next level
(lynda)
If you mention Lynda, why not taking from there?

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 #688 on: December 11, 2016, 12:51:35 AM »
looking for next level
(lynda)
Next level vlookup is index match... Giyf

What's next level pivot? Power pivot? Stay away from that imho... Scary stuff :)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #689 on: December 11, 2016, 11:28:39 AM »
Next level vlookup is index match... Giyf

What's next level pivot? Power pivot? Stay away from that imho... Scary stuff :)
I have a relative who got into power pivot... never heard from him again.

Which is to say that when he starts talking my ear off about power pivot I'm forced to tune him out. ;D

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 #690 on: December 12, 2016, 10:22:05 AM »
Can someone explain how this formula is working? I believe it is being used to return the smallest value in column E.

=LOOKUP(2,1/(E:E<>""),E:E)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #691 on: December 12, 2016, 10:36:06 AM »
Can someone explain how this formula is working? I believe it is being used to return the smallest value in column E.

=LOOKUP(2,1/(E:E<>""),E:E)
Seems to me that it returns the contents of the last cell in the column that is not blank.

The 3 arguments of the function are the lookup value, the lookup vector, and the result vector. So basically we're transforming column E, looking for the value 2 in that transformed vector, and returning the actual value in column E that corresponds to the position we found 2 in the transformed vector. The transformation is basically replacing all non-blank cells with the value 1, and all blank cells with an error. Then, when it looks in that transformed vector for the value 2, it doesn't find it, and instead returns the last position that has a 1 (ie the last non-blank cell).

As far as how the transformation works, E:E<>"" creates a TRUE/FALSE vector for whether each cell is non-blank. 1/ that vector converts the TRUE's into 1's, and the FALSE's into errors. If you'd used * instead of /, you'd get 0's for the FALSE's, and the formula would always return 0.

ETA: After typing all that out, I just googled my description in the first sentence and found this - https://exceljet.net/formula/get-value-of-last-non-empty-cell.

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 #692 on: December 12, 2016, 10:42:30 AM »
Seems to me that it returns the contents of the last cell in the column that is not blank.

The 3 arguments of the function are the lookup value, the lookup vector, and the result vector. So basically we're transforming column E, looking for the value 2 in that transformed vector, and returning the actual value in column E that corresponds to the position we found 2 in the transformed vector. The transformation is basically replacing all non-blank cells with the value 1, and all blank cells with an error. Then, when it looks in that transformed vector for the value 2, it doesn't find it, and instead returns the last position that has a 1 (ie the last non-blank cell).

As far as how the transformation works, E:E<>"" creates a TRUE/FALSE vector for whether each cell is non-blank. 1/ that vector converts the TRUE's into 1's, and the FALSE's into errors. If you'd used * instead of /, you'd get 0's for the FALSE's, and the formula would always return 0.

ETA: After typing all that out, I just googled my description in the first sentence and found this - https://exceljet.net/formula/get-value-of-last-non-empty-cell.
Ok thanks. Google is great too, I should try it more.  :)

Offline JTZ

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2016
  • Posts: 1903
  • Total likes: 228
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #693 on: December 26, 2016, 10:08:25 AM »
I have two different files. One has worksheets A/B/C and the other has D/E/F. How do I make one file to look at all the worksheets?
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 431
  • Total likes: 38
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #694 on: December 26, 2016, 10:19:28 AM »
I have two different files. One has worksheets A/B/C and the other has D/E/F. How do I make one file to look at all the worksheets?

Are you saying file 3 to look at file 1 (ABC) and file 2 (DEF) or file 1 to look at file 2 and vice versa?

Offline JTZ

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2016
  • Posts: 1903
  • Total likes: 228
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #695 on: December 26, 2016, 10:59:22 AM »
File first.xls has three tabs (worksheets?) a/b/c. File second.xls has three tabs d/e/f. I want one file where I can click on any of the six tabs. I need to move or combine them somehow. Hope this is clearer.
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 431
  • Total likes: 38
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #696 on: December 26, 2016, 11:02:25 AM »
File first.xls has three tabs (worksheets?) a/b/c. File second.xls has three tabs d/e/f. I want one file where I can click on any of the six tabs. I need to move or combine them somehow. Hope this is clearer.

Have both of them open.

Highlight the tab(s) that you want to move (by holding down shift and clicking the first and last one - assuming they are in order. If they aren't in order, then hold ctrl and click each one) then right click on one of them and select "move or copy"

A pop up box will appear asking where you would like to move them to and whether you'd like to move them, or just copy them.

Make your selections and hit OK.

Offline JTZ

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2016
  • Posts: 1903
  • Total likes: 228
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #697 on: December 26, 2016, 11:41:28 AM »
Thank you!!!
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

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 #698 on: December 27, 2016, 11:12:03 PM »
Have both of them open.

Highlight the tab(s) that you want to move (by holding down shift and clicking the first and last one - assuming they are in order. If they aren't in order, then hold ctrl and click each one) then right click on one of them and select "move or copy"

A pop up box will appear asking where you would like to move them to and whether you'd like to move them, or just copy them.

Make your selections and hit OK.
Also drag and drop...

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2091
  • Total likes: 65
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #699 on: December 27, 2016, 11:38:10 PM »
i have a big list of names in one column. Is there a way to check if there is any exact name double?