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

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2645
  • Total likes: 48
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1035 on: August 30, 2017, 06:22:08 PM »
Sure can.. file is pretty big though just the text is 26 mb

I'm not an expert, but that's probably your issue.

Offline DanH

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2011
  • Posts: 2175
  • Total likes: 25
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1036 on: August 30, 2017, 06:54:00 PM »
Sure can.. file is pretty big though just the text is 26 mb
you setting proper delineate'rs on import? what version of excel are you using?
For tech help feel free to Telegram me @DanTechSupp

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7246
  • Total likes: 282
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1037 on: August 30, 2017, 07:14:51 PM »
you setting proper delineate'rs on import? what version of excel are you using?
It actually is importing, but it's putting each column of data on a separate row! So there is data in a1,b2,c3 etc.
Something w the mapping probably.
Using 2016

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 6723
  • Total likes: 1530
  • DansDeals.com Hat Tips 43
    • View Profile
    • Kosher Horizons
  • Location: Not Brooklyn
Re: Excel Help and Problems
« Reply #1038 on: September 25, 2017, 01:31:42 PM »
I have a list of 10,000 entries. I only want to keep each 100th. Is there a way to do this easily?
Check out my site for epic kosher adventures: Kosher Horizons

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7246
  • Total likes: 282
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1039 on: September 25, 2017, 01:36:02 PM »
I have a list of 10,000 entries. I only want to keep each 100th. Is there a way to do this easily?
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.

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 6723
  • Total likes: 1530
  • DansDeals.com Hat Tips 43
    • 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: 10037
  • Total likes: 3784
  • DansDeals.com Hat Tips 7
    • 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.
The thing with the hamster is that running on the wheel is actually what it is trying to do.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11026
  • Total likes: 1096
  • 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: 2645
  • Total likes: 48
  • 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: 11026
  • Total likes: 1096
  • 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: 3080
  • Total likes: 231
  • 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: 10037
  • Total likes: 3784
  • DansDeals.com Hat Tips 7
    • 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?
The thing with the hamster is that running on the wheel is actually what it is trying to do.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7246
  • Total likes: 282
  • 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: 10037
  • Total likes: 3784
  • DansDeals.com Hat Tips 7
    • 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?
The thing with the hamster is that running on the wheel is actually what it is trying to do.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7246
  • Total likes: 282
  • 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.