I am currently using the following formula to join text based on the contents of relevant cells. The formula comes from a different thread, and was proposed by JeteMc.
I have also included a test sheet.
Please Login or Register to view this content.
What i need the function to do, is ignore 0's and functions, meaning it should only join text from cells with number values.
Thank you!
Last edited by Allerdrengen; 11-17-2018 at 06:23 AM.
Your sample sheet wasn't hugely helpful. It was easy to add a few zeros... but I wasn't sure what you meant by "ignore functions"
=IFERROR(INDEX(A$9:J$9,AGGREGATE(15,6,COLUMN(A:J)/(A2:J2>0),1))&IF(COUNTIF(A2:J2,">0")>=2,", "&INDEX(A$9:J$9,AGGREGATE(15,6,COLUMN(A:J)/(A2:J2>0),2)),"")&IF(COUNTIF(A2:J2,">0")>=3,", "&INDEX(A$9:J$9,AGGREGATE(15,6,COLUMN(A:J)/(A2:J2>0),3)),"")&IF(COUNTIF(A2:J2,">0")>=4,", "&INDEX(A$9:J$9,AGGREGATE(15,6,COLUMN(A:J)/(A2:J2>0),4)),""),"")
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
Sorry, what I basically mean is that it should ignore everything that is not either a positive number or a negative number.
In your suggestion, it also ignores negative numbers. Any ideas?
And thanks!
So that would automatically leave out any cells with a "function" in them, which gives no return.
I'm out walking. Try it and see what happens.
Try this.
In M2 then dragdown
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thanks for your response!
It almost does the job, however, if a formula returns a blank cell, I would still like it to ignore the cell.
Say I have a formula eg: "=IF(G10=20;40;"")" which would obviously return a blank cell. I need it treat the cell as though it was blank, even though there is a formula in the cell.
Thanks!
Revised formula.
Please Login or Register to view this content.
or if you want a neater solution and don't mind a UDF:
How to install your new codePlease Login or Register to view this content.
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
The use this array formula:
=ConcatAll(IF(Table1[@[Taxes]:[Balance]]<>0,IF(Table1[@[Taxes]:[Balance]]<>"",$A$9:$J$9,""),""),", ")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use 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 brackets yourself - it won't work...
Thanks to the both of you! For now I'll be using the revised formula, but will definitely take a look at the UDF. Thanks!
You're welcome.
A question to the UDF though. I just figured that my actual datasheet is not a "table". So is it possible to do the UDF, without referencing to a table, but just a range of cells?
Thanks!
Code for UDF inColumn M
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 11-18-2018 at 01:21 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks