Excel concatenate strings including if else statements

Question

I have this table

      A      B       C       D
 1   User | priv1 | priv2 | priv3
 2   test |   y   |   n   |   n   
 3   more |   n   |   y   |   n

and so on and I want to generate db2 commands from that.

 ="GRANT "&(IF B2="y", B1, "")& ", "&(IF C2="y", C1, "" )& ", "&(IF D2="y", D1, "" )&" on DATABSE TO USER "&A2&";"

should there for be

 GRANT priv1 ON DATABASE TO USER test;

However I can't get those IF parts to work.

Any idea? I tried many syntaxes but none get it to work. Google wasn't my friend either.

EXCEL 365 on Windows 10

Thanks

Alexey Semerenko · Accepted Answer · 2019-06-21 11:13:38Z

1

if you want multiple "y"'s in cells you can use textJoin function

like this:

="GRANT "&TEXTJOIN(", ";1;IF(B2="y";$B$1;"");IF(C2="y";$C$1;"");IF(D2="y";$D$1;""))&" on DATABASE TO USER "&A2&";"

this formula will place commas if needed

here is working example: https://drive.google.com/open?id=1UmuNpkKQz4KWPJIqT8j8iFO1GB5_J119

UPDATE: if you are able to modify source table better to modify it to this:

      A      B       C       D
 1   User | priv1 |       |     
 2   test | priv2 |       |      
 3   more | priv3 |       |   
 4   other| priv1 | priv3 |

then formula will be more simple like this:

="grant "&TEXTJOIN(", ";1;B2:D2)&" on database to user "&A2&";"

here is the example: https://drive.google.com/open?id=1IiSmT3OYLpxMvaeG0KYgK17ZYengWxWu

edited Jun 21, 2019 at 11:13

answered Jun 21, 2019 at 11:07

Alexey Semerenko

1321 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

arenginsm na Over a year ago

I get the #NAME? if I use that

Alexey Semerenko Over a year ago

could you please share file?

Harassed Dad · Accepted Answer · 2019-06-21 11:11:03Z

0

Brackets after the IF

 ="GRANT "&IF(B2="y", B1, "")& ", "&IF(C2="y", C1, "" )& ", "&IF(D2="y", D1, "" )&" on DATABASE TO USER "&A2&";"

EDIT: And quotes aropund the text in B1,C1, D1 and A2

="GRANT "&IF(B2="y", "'"&B1&"'", "")& ", "&IF(C2="y", "'"&C1&"'", "" )& ", "&IF(D2="y","'"&D1&"'", "" )&" on DATABASE TO USER '"&A2&"';"

edited Jun 21, 2019 at 11:11

answered Jun 21, 2019 at 10:49

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

4 Comments

Harassed Dad Over a year ago

It works for me - what's the error? - did you copy and paste the formula?

JvdV Over a year ago

Try a semicolon as seperator symbol?

arenginsm na Over a year ago

Where should I put the semicolon

Harassed Dad Over a year ago

sorry - I forgot to test this with your actual data so i didn't notice the text needing quote marks

Collectives™ on Stack Overflow

Excel concatenate strings including if else statements

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related