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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3263
  • Total likes: 473
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #795 on: February 10, 2017, 01:15:01 PM »
First step is to separate the names from the addresses/phone numbers. Assuming your text is in column A , the first formula gives you the name in one column by itself, and the second gives you the address and phone number together.

=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))
=RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

Separating out the pieces of the second formula will depend on how it's set up. You might be able to use text to columns there (like if the phone numbers start with a parenthesis).

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1752
  • Total likes: 220
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #796 on: February 10, 2017, 02:21:59 PM »
I have an excel sheet that in the same column there are names, addresses and phone numbers. I would like to have them in separate columns. I tried a text to columns but there seems to be no way of splitting it when it hits a numerical value. ( The beginning of the address )
If you have Excel 2016, not sure if 2013 too, you can do this with Flash Fill. Just do yourself a few examples and he'll usually be able to finish the rest.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2654
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #797 on: February 12, 2017, 12:19:52 PM »
If you have Excel 2016, not sure if 2013 too, you can do this with Flash Fill. Just do yourself a few examples and he'll usually be able to finish the rest.
2013 has it too. Sometimes it's awesome, and sometimes it just can't figure it out.

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 #798 on: February 14, 2017, 05:06:58 PM »
How to subtract a group of cells from a fixed number?
My n00b attempt: =15000 - (B3:B10)
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12009
  • Total likes: 2058
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #799 on: February 14, 2017, 05:18:17 PM »
How to subtract a group of cells from a fixed number?
My n00b attempt: =15000 - (B3:B10)
Do you want to first total B3:B10, and then subtract that from 15000?
If so:
=15000-SUM(B3:B10)

Workflowy. You won't know what you're missing until you try it.

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 #800 on: February 14, 2017, 05:34:53 PM »
Not working for some reason.
Copy and paste from fx box: =15000-SUM(B3:B10)
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1752
  • Total likes: 220
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #801 on: February 14, 2017, 06:00:14 PM »
Not working for some reason.
Copy and paste from fx box: =15000-SUM(B3:B10)
Works fine for me. Can you upload a sample Excel file?

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 #802 on: February 14, 2017, 06:11:09 PM »
Can I just attach a file?
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12009
  • Total likes: 2058
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #803 on: February 14, 2017, 07:09:26 PM »
Works fine for me.
+1

What results do you get?
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12275
  • Total likes: 6750
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #804 on: February 14, 2017, 07:11:07 PM »
Can I just attach a file?
Do it in a new sheet. There is something hidden there.
Feelings don't care about your facts

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12275
  • Total likes: 6750
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #805 on: February 14, 2017, 07:15:57 PM »
You had the formula in that cell 3 times including with spaces in the middle
Feelings don't care about your facts

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 #806 on: February 14, 2017, 07:24:42 PM »
 :-[
Thank you! At least I tried before asking.  :)
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1515
  • Total likes: 53
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Problem
« Reply #807 on: February 14, 2017, 08:41:11 PM »
I may have got it.
I'll elaborate in a bit
I was running into a error, trying to calculate a Sum Total that includes a percentage of itself as one of the items.

The simplest solution was to go to Options > Formulas > Enable Iterative Calculations.

You need to remember to disable it after though.

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 #808 on: April 02, 2017, 07:42:20 AM »
I have a couple of 100k rows of data that I am pivoting. My PC/Excel is just about handling it.. crashes etc.

Does saving the data as CSV, and using a separate Excel WB to pivot and analyse​ make things better of worse?

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 #809 on: April 02, 2017, 10:21:59 AM »
I have a couple of 100k rows of data that I am pivoting. My PC/Excel is just about handling it.. crashes etc.

Does saving the data as CSV, and using a separate Excel WB to pivot and analyse​ make things better of worse?

No difference imho. You're better off in access and linking your excel to the access DB.