Re: [Solved] Re: [libreoffice-users] "Case" function equivalent in Calc

Hi Tom.

Well, not quite.  In my particular case this formula worked.  But with 
some off-list input from smarter brains than mine, I realize it wouldn't 
work under many situations.

It turns out the formula would need to change a bit, otherwise what I 
did would leave a trailing semi-colon where column X is blank.  In fact, 
it's a tricky situation b/c a semi-colon is needed after U3 if U3 has a 
value AND any of the others has a value, otherwise not. Then, if V3 has 
a value and any of the other later cells has a value, there should be 
another semi-colon.  Etc. etc.  In the last case, a semi-colon should 
follow W3 only if there's a value in X3.

I've tried a bunch of formulas and the closest I've come is the following:

=A13&IF(NOT(ISBLANK(A13))AND(NOT(ISBLANK(B13)))OR(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&B13&IF(NOT(ISBLANK(B13))AND(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&C13&IF(NOT(ISBLANK(C13))AND(NOT(ISBLANK(D13))),";","")&D13

But that clearly doesn't work.  Note I'm working in a test sheet and am 
using different cells.  This formula leaves leading semi-colons under a 
number of situations and double semi-colons under others. I'm not 
entirely sure CASE would work any better here though.

Anyone want to help out, great.  But it's a pretty specialized 
situation, so I understand if not.

Carl


On 5/16/13 3:02 AM, Tom Davies wrote:
> Hi :)
> WoooHooo!!  Nicely done! :)  Is this whole thread solved now?  
> COngrats of so!
> Regards from
> Tom :)
>
>
>
>     ------------------------------------------------------------------------
>     *From:* Carl Paulsen <carlpaulsen@comcast.net>
>     *To:* users@global.libreoffice.org
>     *Sent:* Thursday, 16 May 2013, 1:44
>     *Subject:* Re: [libreoffice-users] "Case" function equivalent in Calc
>
>     At long last I got this to work.  Syntax is "ISBLANK" and not
>     "ISEMPTY"
>     - aaarrrrgggghhhh.  My Filemaker days are getting in my way.  Replace
>     all "isempty" below with "ISBLANK" and it works perfectly now.
>
>     Carl
>
>
>     On 5/15/13 6:38 PM, Carl Paulsen wrote:
>     > So in the absence of a Case function, here's what I've done so
>     far as
>     > a calculated solution.  Note that the data I want to concatenate
>     is in
>     > cells U3, V3, W3, and X3.  In Y3, I put the following:
>     >
>     > =U3 & IF(NOT(isempty(U3)),";","") & V3 &
>     IF(NOT(isempty(V3)),";","") &
>     > W3 & IF(NOT(isempty(W3)),";","") & X3
>     >
>     > The idea is that I put together U3, a semicolon if U3 isn't
>     empty (and
>     > nothing if it is), V3 and a semicolon if V3 isn't empty, W3 and a
>     > semicolon if W3 isn't empty, and X3.  If any of the cells is empty,
>     > nothing will be added until the next cell that has data.
>     >
>     > Unfortunately, I'm getting a #NAME? error.  I'm assuming some
>     kind of
>     > syntax error.  Any words of wisdom?
>     >
>     > Thanks a ton all.
>     > Carl
>     >
>     >
>     >
>     > On 5/15/13 5:13 PM, Dan Lewis wrote:
>     >> On 05/15/2013 04:16 PM, Carl Paulsen wrote:
>     >>> Anyone know if there is an equivalent to the Filemaker Pro "Case"
>     >>> function?  It's kinda like the "IF" function but is simpler to
>     >>> concatenate multiple conditions. It basically says If
>     something is
>     >>> true then do what is specified, if the next thing is, then do
>     that,
>     >>> if the next thing is true, do that, etc.
>     >>>
>     >>> Here's what I need to do.  Take 4 columns and concatenate with a
>     >>> semi-colon between the values, but not string together two
>     >>> semi-colons consecutively.  Like:
>     >>>
>     >>> Phone    Email    Mail -> Phone;Email;Mail
>     >>> Phone                  Mail -> Phone;Mail
>     >>>                            Mail -> Mail
>     >>>              Email    Mail -> Email;Mail
>     >>>
>     >>> So semi-colons only occur if there's a value present and not
>     at all
>     >>> if there's only one value present.
>     >>> I hope that makes sense and displays correctly.
>     >>>
>     >>> Carl
>     >>      I just checked the available functions in Calc. The "Case"
>     >> function does not appear among them. "Case When" is available
>     in Base
>     >> database queries though.
>     >>
>     >> --Dan
>     >>
>     >
>     >
>
>     -- 
>
>     Carl Paulsen
>
>     8 Hamilton Street
>
>     Dover, NH 03820
>
>     (603) 749-2310
>
>
>     -- 
>     To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
>     <mailto:unsubscribe@global.libreoffice.org>
>     Problems?
>     http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>     Posting guidelines + more:
>     http://wiki.documentfoundation.org/Netiquette
>     List archive: http://listarchives.libreoffice.org/global/users/
>     All messages sent to this list will be publicly archived and
>     cannot be deleted

--

Carl Paulsen

8 Hamilton Street

Dover, NH 03820

(603) 749-2310


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted