Pull non-blank values from a range and copy those values into one cell separated by commas

Hello!
Thanks so much for your help.

Please see the picture - I have a survey of questions. Not all of the questions will have responses.

I want to pull only the values from those cells with responses into one cell. And I want those values to be separated by commas.

I've experimented with index and match with limited success, but I'm thinking I might need isblank and/or other functions.

I would like a solution in which I can apply this formula to a range as large as 20 questions.

Thank you!

Excel_Problem.jpg

Here is a VBA solution (actually, a UDF) from another source. Instructions on how to use it are on that page as well. It is pretty simple to implement following them.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=817

- Moo

Install the VBA function from this link:

https://www.excelforum.com/showthread.php?p=3096647

Then, to use it:

Data Range

	A	B	C	D	E
1	Question 1	Question 2	Question 3	Question 4	Question 5
2		Yes		No	No
3
4
5
6
7	Yes, No, No

Enter this array formula** in A7:

=concatall(IF(A2:E2<>"",A2:E2,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Basically, if you follow the directions from that page you will be inserting VBA code into a new module within that workbook:

Option Explicit
 
Function ConcRange(Substrings As Range, Optional Delim As String = ", ", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = True)
          
    Dim CLL As Range
     
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
     
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
     
End Function

That should do the trick. Once you go back into the Excel view, all you would have to do then is enter this formula in a blank cell (assuming your range is in cells A2:E2):

=ConcRange(A2:E2)

* Of course, if you plan to have varying # of responses, you could modify that function to something like:

Formula:

=ConcRange(A2:INDEX(2:2,1,MATCH(REPT("z",99),2:2)))

That will concatenate all cells in row 2 from A2 thru the last column with a text value in it.

- Moo

Formula:

=if(isblank(a2),"",a2&", ")&if(isblank(b2),"",b2&", ")&if(isblank(c2),"",c2&", ")&if(isblank(d2),"",d2&", ")&if(isblank(e2),"",e2&"")

Originally Posted by Brendan_Floyde

Formula:

=if(isblank(a2),"",a2&", ")&if(isblank(b2),"",b2&", ")&if(isblank(c2),"",c2&", ")&if(isblank(d2),"",d2&", ")&if(isblank(e2),"",e2&"")

That may work for a couple of cells, Brendan_Floyde, but if there are 10, 20, heck, if there are 50 survey questions, that is going to be one ridiculously long formula. =)

- Moo

Thanks to everyone!

Moo, I used that code and it worked! Thank you! I do have one question - the code only worked when it was saved as a VBA module. And even then it had to be saved as "sai_ConcRange" per the attachment in the zip file found at the bottom of that page. If it was named as "ConcRange", it did not work. Do you have any idea about why that is? What is that "sai_" in front of the module name and why is it required?

Tony, thank you for that code. I decided not to use it, though, because I found Moo's VBA to be a little simpler. I'm not very good at VBA yet, and the code you sent along is just way above my level at this point. Thanks again, though.

Brendan - thank you as well. This is the non-VBA solution I was trying to come up with on my own and I see you did it easily. I was trying with match and index, but your solution makes a lot of sense and is very handy for just a few cells.

dos mios - very clever, thank you. If I'm understanding correctly, you're suggesting doing a find and replace on a tab-delimited file from excel and replacing those with tabs with commas?

Thanks again to all!

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.