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

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 7509
  • Total likes: 2885
  • DansDeals.com Hat Tips 44
    • View Profile
    • Kosher Horizons
  • Location: Not Brooklyn
Re: Excel Help and Problems
« Reply #1040 on: September 25, 2017, 01:43:16 PM »
Are they unique in anyway? If yes,simply sort by them and delete rest.

If not, make them unique by adding a column of numbers, count 1-100, then repeat this again. Then auto complete down should give you a sets of 1-100.

It's a list of coordinates, but I don't need such a high level of precision. Your second suggestion worked great, thanks.
Check out my site for epic kosher adventures: Kosher Horizons

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1041 on: September 25, 2017, 03:31:50 PM »
It's a list of coordinates, but I don't need such a high level of precision. Your second suggestion worked great, thanks.
Alternatively you can enter references into some other cells.

=a1
=a100
=a200
=a300
etc.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1042 on: September 25, 2017, 03:43:37 PM »
Alternatively you can enter references into some other cells.

=a1
=a100
=a200
=a300
etc.
You could even use INDIRECT to get the reference and then copy the formula down

Example: (assuming the data you want to copy is in Col B and you are working in Col A)
=INDIRECT("B"&ROW(A1)*100)
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1043 on: September 25, 2017, 06:47:10 PM »
Another way:

=MOD(ROW(A1),100)=0

You'll get true or false, just filter for true (or filter for false and delete those rows).

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1044 on: September 25, 2017, 08:04:19 PM »
Another way:

=MOD(ROW(A1),100)=0

You'll get true or false, just filter for true (or filter for false and delete those rows).
Another great solution!

While we're at it, you can do this with VBA too! :)
Workflowy. You won't know what you're missing until you try it.

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 Help and Problems
« Reply #1045 on: September 25, 2017, 11:50:53 PM »
While we're at it, you can do this with VBA too! :)
Reminds me of my internship, when my boss had me convert a new mortality table into life expectancies using native excel functions, VBA, and APL separately. Had to base my end of summer presentation on a short 1 week side project, because saying you did the same thing 3 different ways kind of makes it sound like you didn't accomplish anything all summer. ;D

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1046 on: September 26, 2017, 06:59:53 PM »
Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?
Feelings don't care about your facts

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 Help and Problems
« Reply #1047 on: September 26, 2017, 07:16:56 PM »
Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?
Yes, u need to do text to column​ trick.

It refreshes whole column.

(I do this all the time so I recorded it as a macro.. looking for a relative reference macro though... Didn't have time to do it.)

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1048 on: September 26, 2017, 07:19:53 PM »
Yes, u need to do text to column​ trick.

It refreshes whole column.

(I do this all the time so I recorded it as a macro.. looking for a relative reference macro though... Didn't have time to do it.)

What is the trick?
Feelings don't care about your facts

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 Help and Problems
« Reply #1049 on: September 26, 2017, 07:22:16 PM »
What is the trick?
Text to columns, (option in data ribbon ) but don't select anything ( tab, space etc) as the deliminator, so it all stays in same column.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1050 on: September 27, 2017, 02:02:30 PM »
Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub

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 Help and Problems
« Reply #1051 on: September 27, 2017, 02:56:48 PM »
I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1052 on: September 27, 2017, 02:58:45 PM »
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.

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 Help and Problems
« Reply #1053 on: September 27, 2017, 03:14:28 PM »
Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.
Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1534
  • Total likes: 58
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1054 on: October 30, 2017, 09:40:21 AM »
Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc
I didn't want to use a macro on the QAT either, so what I did was add the "Paste as Value" button to the Quick access toolbar in the 1st position (needs to be added from settings).

Now I have an easy shortcut: Alt + 1 (or whatever position it is on the QAT).

Bonus is that it works great if I want to copy the value only to a new sheet.

Same goes for pretty much any function that I use often, they end up as a shortcut in the QAT, and I don't need to remember my keyboard shortcuts as excel shows you a hint every time you press just ALT.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1055 on: October 30, 2017, 09:56:05 AM »
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)
THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.
Feelings don't care about your facts

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 Help and Problems
« Reply #1056 on: October 30, 2017, 10:03:47 AM »
THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.
U paste in place

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1057 on: October 30, 2017, 10:53:19 AM »
U paste in place
By dragging and dropping?
Feelings don't care about your facts

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 Help and Problems
« Reply #1058 on: October 30, 2017, 10:55:57 AM »
By dragging and dropping?
Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do :)

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1059 on: October 30, 2017, 11:36:10 AM »
Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do :)
dunno, pasting in place ids not working by me.
Feelings don't care about your facts