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

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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]
טו, ?