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

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 1941
  • Total likes: 87
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #880 on: April 30, 2017, 07:52:12 AM »
I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.

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 Problem
« Reply #881 on: April 30, 2017, 08:33:57 AM »
I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.
Looks like something funny is going on. Can you attach your excel file?

You could also try copying and doing Paste Values into another excel sheet and then try copying that into Word.
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: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #882 on: April 30, 2017, 09:56:26 AM »
I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.
My guess is that you have some merged cells
Feelings don't care about your facts

Offline Yammer

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2015
  • Posts: 3697
  • Total likes: 215
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #883 on: May 08, 2017, 05:08:04 PM »
I have an Google sheet that I'm putting in name/address information. I currently have a data validation list of all the state abbreviations.

Is there a way for the person entering the data to see the full state name but once selected, the abbreviation comes up?

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #884 on: May 14, 2017, 07:03:35 PM »
I have excel data which I need sorted alphabetically.
Data is in hebrew
is there a way to get it to sort
יג, יד, טו, טז, יז...

(I'd try to create a unique order list, kind of 'sort array' then sort by that column, but I think that'll only work if the entire cell is the specific item match. here I need it to treat it that way 'alphabetically'-where it's the beginning of a larger string of data (e.g. טז,כ comes after יד, ח)

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 Problem
« Reply #885 on: May 14, 2017, 07:28:41 PM »
I have excel data which I need sorted alphabetically.
Data is in hebrew
is there a way to get it to sort
יג, יד, טו, טז, יז...

(I'd try to create a unique order list, kind of 'sort array' then sort by that column, but I think that'll only work if the entire cell is the specific item match. here I need it to treat it that way 'alphabetically'-where it's the beginning of a larger string of data (e.g. טז,כ comes after יד, ח)
Maybe a hidden sheet with a list of hebrew #s and regular #s? Then you could vlookup to get the # and sort by the vlookup column.
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #886 on: May 14, 2017, 07:30:11 PM »
Maybe a hidden sheet with a list of hebrew #s and regular #s? Then you could vlookup to get the # and sort by the vlookup column.
that's what I was referring to in the second half of my post. I've gotten stuff like that to work on full cells, not where "cell starts with..."

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 Problem
« Reply #887 on: May 14, 2017, 07:31:14 PM »
that's what I was referring to in the second half of my post. I've gotten stuff like that to work on full cells, not where "cell starts with..."
What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #888 on: May 14, 2017, 07:35:58 PM »
What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"
see here

בראשית   ב, י
שמות   יט, ג
תהלים   קיג, ט
בראשית   ב, כא-כב
משלי   כד, ג-ד
שמות   יד, א
שמות   לה, כב
בראשית   ג, ו
בראשית   ג, טז-יט
בראשית   ג, כב-כג
שמות   לה, כב
שמות   לה, כה-כו
תהלים   קכו, ה
משלי   ג, ו
ישעיהו   נז, טז
שמות   טו, כ-כא
בראשית   ב, א-ג
שיר השירים   ג, יא


I have the 'sefer' in its own cell, sorted by custom lookup. 2nd column sorts great alphabetically except for where שמות   טו, כ-כא will be listed before שמות   יד, א

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #889 on: May 14, 2017, 07:37:59 PM »
What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"
I hadn't had that work for me (in the sefer cell either) though I may try again. If yes-that would work for the פרק (though not for the פסוק).
what do seforim people do?

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 Problem
« Reply #890 on: May 14, 2017, 07:52:57 PM »
see here

בראשית   ב, י
שמות   יט, ג
תהלים   קיג, ט
בראשית   ב, כא-כב
משלי   כד, ג-ד
שמות   יד, א
שמות   לה, כב
בראשית   ג, ו
בראשית   ג, טז-יט
בראשית   ג, כב-כג
שמות   לה, כב
שמות   לה, כה-כו
תהלים   קכו, ה
משלי   ג, ו
ישעיהו   נז, טז
שמות   טו, כ-כא
בראשית   ב, א-ג
שיר השירים   ג, יא


I have the 'sefer' in its own cell, sorted by custom lookup. 2nd column sorts great alphabetically except for where שמות   טו, כ-כא will be listed before שמות   יד, א
hmm, that does complicate things. you might need to use formulas to separate out the perek and pasuk.
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #891 on: May 14, 2017, 08:12:54 PM »
wondering about an alternate possibility: run a "replace all" before and after sorting. (e.g. all טו=יה, sort, then in reverse) considering that i  don't expect examples of the alternate word.

problem: where alternate word exists within larger word (e.g. ישעיהו). in theory searching for "whole word only" should solve 90% of the issue, but I don't see such a choice in excel (only "whole cell). and searching with space preceding won't work for where the cell begins that way.

Is there a way to search "whole world only" or a wildcard that means "no character" (kinda reverse of *)
?

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 Problem
« Reply #892 on: May 14, 2017, 08:27:46 PM »
wondering about an alternate possibility: run a "replace all" before and after sorting. (e.g. all טו=יה, sort, then in reverse) considering that i  don't expect examples of the alternate word.

problem: where alternate word exists within larger word (e.g. ישעיהו). in theory searching for "whole word only" should solve 90% of the issue, but I don't see such a choice in excel (only "whole cell). and searching with space preceding won't work for where the cell begins that way.

Is there a way to search "whole world only" or a wildcard that means "no character" (kinda reverse of *)
?
could you transfer this to google sheets? If so, regex would open up a lot of possibilities
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #893 on: May 14, 2017, 10:29:12 PM »
could you transfer this to google sheets? If so, regex would open up a lot of possibilities
tell me more

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 Problem
« Reply #894 on: May 14, 2017, 11:01:39 PM »
tell me more
You could use regexextract to split "בראשית   ב, י" into 3 cells, then you'd be able to do the vlookup on the ב cell and the י cell and sort.

Another option would be to use regexreplace to convert טו and טז into their equivalents, for sorting purposes.
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #895 on: May 15, 2017, 11:55:52 AM »
I find it interesting that gdocs has regex where excel does not (especially considering word does IINM)
here I was thinking that excel is the more robust program with gdocs being more lightweight...

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 Problem
« Reply #896 on: May 15, 2017, 11:57:17 AM »
I find it interesting that gdocs has regex where excel does not (especially considering word does IINM)
here I was thinking that excel is the more robust program with gdocs being more lightweight...
+1 it was very surprising to me as well
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #897 on: May 15, 2017, 11:57:54 AM »
+1 it was very surprising to me as well
am I wrong about gdocs in general?

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 Problem
« Reply #898 on: May 15, 2017, 12:00:22 PM »
am I wrong about gdocs in general?
IMO Excel is more robust, and also more accepted in the business world. Though it's possible that over time gdocs has been adding features and catching up somewhat. Personally, I turn to google sheets in 3 situations:
1) I want to collaborate with other people
2) I want to access the file from any computer (eg: at home / at work)
3) I want to use regex
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3726
  • Total likes: 341
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #899 on: May 15, 2017, 12:09:24 PM »
You could use regexextract to split "בראשית   ב, י" into 3 cells, then you'd be able to do the vlookup on the ב cell and the י cell and sort.

Another option would be to use regexreplace to convert טו and טז into their equivalents, for sorting purposes.
How does regex work with hebrew?
shouldn't
Code: [Select]
\bטו\bfind th טו in
Code: [Select]
טו, ?