How do I conditionally add a new line and tabs in Excel? -


i have list of values id, name, category, description , variable amount of keyword values; between 0 , 18 each row. want create list of values in form of:

(id, 'keyword') , (id, 'keyword')

where list increments if there keyword go identifier. meant easy manual list sql insert statement.

i realize can use &char(9) inserting tabs , &char(10) inserting new lines, , sequence proper tabulation &char(10)&char(9)&char(9) each new entry.

=if(g2<>"",concatenate("(",a2,", '",upper(g2),"')"),"") &char(10)&char(9)&char(9)& if(h2<>"",concatenate("(",a2,", '",upper(h2),"')"),"")

i've tried several different combinations such as:

=if(g2<>"",concatenate("(",a2,", '",upper(g2),"')"),"")+ if(h2<>"",char(10)&char(9)&char(9)&concatenate("(",a2,", '",upper(h2),"')"),"")

and

=if(g2<>"",concatenate("(",a2,", '",upper(g2),"')",char(10),char(9),char(9)),"")+ if(h2<>"",concatenate("(",a2,", '",upper(h2),"')"),"")

and

=if(g2<>"",concatenate("(",a2,", '",upper(g2),"')"),"")+ if(h2<>"",concatenate(char(10),char(9),char(9),"(",a2,", '",upper(h2),"')"),"")

which give errors in calculation. has else been dying know how , had kind of frustration? have solution this?

i figured out within hour of posting, neglected post solution here in case else wanted know.

you have concatenate whole series of if blocks , add char(10) , char(9)s inner concatenate blocks, so:

=concatenate(if(g2<>"",concatenate("(",a2,", '",upper(g2),"')"),""), if(h2<>"",concatenate(char(10),char(9),char(9),"(",a2,", '",upper(h2),"')"),""))

thanks,

-c§


Comments

Post a Comment

Popular posts from this blog

php - Wordpress website dashboard page or post editor content is not showing but front end data is showing properly -

Image
recently have uploaded wordpress site onto server. have uploaded wordpress site in sub-folder under root folder ../root(domain root folder)/wordpress (where host wordpress folder). now problem content data in page or post in dashboard of wordpress admin panel not showing - see image. but data showing in front view - see image. if not browser cache issue , not issue happened tinymce editor during upgrade; try add config file: define('concatenate_scripts', false);
Read more

authentication - Mongodb revoke acccess to connect test database -

i have ec2 instance mongodb 3.2.0 installed. have user admin access. can connect user , perform operation remotely. 1 can connect database test remote if knows ip mongo 11.11.11.11. although not able make operations, want restrict access user credentials can connect server. first need create users , grant roles users, example: use admin db.createuser( { user: "myuseradmin", pwd: "abc123", roles: [ { role: "useradminanydatabase", db: "admin" } ] } ) then need start server --auth parameter mongod --auth more information here
Read more

python - Scipy.Odr multiple variable regression -

i perform multidimensional odr scipy.odr . read api documentation, says multi-dimensionality possible, cannot make work. cannot find working example on internet , api crude , give no hints how proceed. here mwe: import numpy np import scipy.odr def linfit(beta, x): return beta[0]*x[:,0] + beta[1]*x[:,1] + beta[2] n = 1000 t = np.linspace(0, 1, n) x = np.full((n, 2), float('nan')) x[:,0] = 2.5*np.sin(2*np.pi*6*t)+4 x[:,1] = 0.5*np.sin(2*np.pi*7*t + np.pi/3)+2 e = 0.25*np.random.randn(n) y = 3*x[:,0] + 4*x[:,1] + 5 + e print(x.shape) print(y.shape) linmod = scipy.odr.model(linfit) data = scipy.odr.data(x, y) odrfit = scipy.odr.odr(data, linmod, beta0=[1., 1., 1.]) odrres = odrfit.run() odrres.pprint() it raises following exception: scipy.odr.odrpack.odr_error: number of observations not match which seems related matrix shapes, not know how must shape properly. know? firstly, in experience scipy.odr uses arrays, not matrices. library seems make large...
Read more