if then statement to return text from cells satisfying condition
 

HI, I need to check the contents of 29 columns and return the text
values of all the non-zero cells in that row.I am trying to run an if-
then statement to acheive the following:

if (B1:AD1 1, (return the text within the cells as multiple strings
separated by commas),0)
I need help on how to acheive this result. I tried concatenation but
it wasn't working out.

Thanks

if then statement to return text from cells satisfying condition
 

Download the free Morefunc.xll addin from he
http://download.cnet.com/Morefunc/30...-10423159.html

and use this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF(ISTEXT(A1:G1),A1:G1,"" )," "))," ",",")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... after
you have adjusted the ranges to suit.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123925

if then statement to return text from cells satisfying condition
 

I tried doing this and the resulting cell looks blank. Should I be
running any of the extra functions that I added in, for this to work?
On Aug 10, 9:59*am, NBVC wrote:
Download the free Morefunc.xll addin from hehttp://download.cnet.com/Morefunc/30...-10423159.html

and use this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF(ISTEXT(A1:G1),A1:G1,"" )," "))," ",",")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... after
you have adjusted the ranges to suit.

--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile:http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=123925

if then statement to return text from cells satisfying condition
 

After you enter the formula, you need to hold the CTRL and SHIFT keys
down and then press ENTER.. You should see { } brackets appear around
the formula.. and the result should be visible in the cell.

Savvy;447518 Wrote:
I tried doing this and the resulting cell looks blank. Should I be
running any of the extra functions that I added in, for this to work?
On Aug 10, 9:59 am, NBVC wrote:
Download the free Morefunc.xll addin from he'Morefunc - Free
software downloads and software reviews - CNET Download.com'
(http://download.cnet.com/Morefunc/30...-10423159.html)

and use this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF(ISTEXT(A1:G1),A1:G1,"" )," "))," ",",")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER...
after
you have adjusted the ranges to suit.

--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile:'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread:'if then statement to return text from cells
satisfying condition - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=123925)


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123925

if then statement to return text from cells satisfying condition
 

Thanks. It worked!

On Aug 10, 10:31*am, NBVC wrote:
After you enter the formula, you need to hold the CTRL and SHIFT keys
down and then press ENTER.. You should see { } brackets appear around
the formula.. and the result should be visible in the cell.

Savvy;447518 Wrote:





I tried doing this and the resulting cell looks blank. Should I be
running any of the extra functions that I added in, for this to work?
On Aug 10, 9:59 am, NBVC wrote:
Download the free Morefunc.xll addin from he'Morefunc - Free
software downloads and software reviews - CNET Download.com'
(http://download.cnet.com/Morefunc/30...-10423159.html)

and use this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF(ISTEXT(A1:G1),A1:G1,"" )," "))," ",",")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER...
after
you have adjusted the ranges to suit.

--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile:'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread:'if then statement to return text from cells
satisfying condition - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=123925)

--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile:http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:http://www.thecodecage.com/forumz/sh....php?t=123925- Hide quoted text -

- Show quoted text -