Concatenate with a Line Break BUT....

[calinini]

Hello!

I have a exit interview survey that individuals are able to select multiple reasons as to why they are leaving the company. When I pull the raw data it puts all the reasons they selected into a new column. I would like to put all the reasons into one cell, which I can do using the CHAR(10) function, however if the person did not select a reason, it will still put a line break there.

Example Data:

A	B	C	D	E	F	G	H
CHOICES (Column)/EE Name (Row)	Better pay	Promotional position with more pay and/or responsibility	Better benefits package to include tuition reimbursement, medical, 401k and vacation	Better schedules	Less responsibility for equal pay or more pay	Other (please specify)	<B>RESULTS</B>
EE A				Better schedules		Other (please specify)	Better schedules Other (please specify)
EE B	Better pay	Promotional position with more pay and/or responsibility			Less responsibility for equal pay or more pay		Better pay Promotional position with more pay and/or responsibility Less responsibility for equal pay or more pay
EE C	Better pay	Promotional position with more pay and/or responsibility	Better benefits package to include tuition reimbursement, medical, 401k and vacation				Better pay Promotional position with more pay and/or responsibility Better benefits package to include tuition reimbursement, medical, 401k and vacation

[/tr]

My Formula in Col H is:
=CONCATENATE(a1,CHAR(10)&b1,CHAR(10)&c1,CHAR(10)&d1,CHAR(10)&e1,CHAR(10)&f1,CHAR(10)&g1)

As you can see it puts line breaks regardless to if that cell has value.

I would like the results of EE A to be:

Better schedules
Other (please specify)

So if that person didn't select that reason, it will not give it a line break. I have a feeling this is a macro or a VERY long If statement, which I'm not opposed to but I have to manually do this report monthly and I'm tired of copying and pasting. LOL

OR, if someone can tell me how to do a REPLACE function for a line break, that would work too!

Your help is greatly appreciated!

TIA!

 

[Takae]

hope this helps.

Sub sample1()
    Dim i As Long, str As String
    Dim rng As Range, C
    
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range(Cells(i, 2), Cells(i, 7))
        For Each C In rng
            If C <> "" Then
                str = str & vbCrLf & C
            End If
         Next
            Cells(i, 8).Value = Right(str, Len(str) - 2)
        str = ""
    Next
    
End Sub

 

[calinini]

Thank you Takae for your response.

I think I might be in a little over my head though. I've created your Macro in excel. I'm unsure of what I need to update in it though to make it work.

In my example, in column H, I want to put all the information in columns B:G for each row respectively.

How do I apply your macro so it will do that? It doesn't ask me which columns I would like to concatenate or anything of that sort.

Your help is appreciated.

 

[calinini]

Hi Takae, I just realized that your code does exactly what I need. THANK YOU!

As you can imagine my spreadsheet contains more information than my example. In reality, I'm trying to concatenate columns l:V into Column W. If you could show your code with those changes, I can do a comparison and see what you updated to indicate which columns to pull.

Thank you again!! I appreciate your help.

 

[Takae]

Column A is column H in reality?

Sub sample1()
Dim i As Long, str As String
Dim rng As Range, C

For i = 2 To Cells(Rows.Count, 8).End(xlUp).Row
Set rng = Range(Cells(i, 9), Cells(i, 22))
For Each C In rng
If C <> "" Then
str = str & vbCrLf & C
End If
Next
Cells(i, 23).Value = Right(str, Len(str) - 2)
str = ""
End Sub