Hi
I have data sitting in 4 rows per record thats exclusive.
i.e. the question is about drinks but from 4 questions.
In Q1, A1 has tea, then row B1,C1,D1 have a 0
In Q2, B2 has Cocoa, then A2,C2, D2 have 0
In Q3, C3 has Soya, then A3,B3, D3 have 0
I would like to combine only the text in one row at the end, and leave out the zeros
Excel Formulae.jpg
Many Thanks
Excel upload not working
Try this in F2 and drag down
=SUBSTITUTE(CONCATENATE(B2,C2,D2,E2),0,"")
Happy with my advice? Click on the * reputation button below
Your explanation is a bit confusing, almost as though you are mixing up columns and rows? However to achieve your aim;
For rows use
Or for columns use
Please Login or Register to view this content.
Please Login or Register to view this content.
Isskint, i get satisfaction out of helping others
... OR THIS ARRAY FORMULA:
=INDEX(B2:E2,MATCH(0,INDEX(IF(B2:E2<>0,0),0),0))
Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks, This works ...
You're welcome.
This also works, just to be specific. You have my upvote as wellOriginally Posted by Crooza
Works, and Thanks for the variant for columns. ++Your explanation is a bit confusing, almost as though you are mixing up columns and rows? However to achieve your aim;
For rows use
Or for columns usePlease Login or Register to view this content.
Please Login or Register to view this content.
Great. Glad it worked for you and thanks for the rep points
A perfect poster, who gives feedback to everyone that helped. Nice by name and nice by nature!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks