Concatenate with a Line Break BUT....

calinini

New Member
Joined
Jul 13, 2015
Messages
6
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:

ABCDEFGH
CHOICES (Column)/EE Name (Row)Better payPromotional position with more pay and/or responsibilityBetter benefits package to include tuition reimbursement, medical, 401k and vacationBetter schedulesLess responsibility for equal pay or more payOther (please specify)<B>RESULTS</B>
EE ABetter schedulesOther (please specify)


Better schedules

Other (please specify)
EE BBetter payPromotional position with more pay and/or responsibilityLess 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 CBetter payPromotional position with more pay and/or responsibilityBetter 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

Well-known Member
Joined
Jul 1, 2015
Messages
674
hope this helps.

Code:
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

New Member
Joined
Jul 13, 2015
Messages
6
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

New Member
Joined
Jul 13, 2015
Messages
6
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

Well-known Member
Joined
Jul 1, 2015
Messages
674
Column A is column H in reality?
Code:
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
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top