Thread: Consolidate text from different Columns in one column with respective heading

Hi,

I am looking for a macro which can ease my work of consolidating responses from different products/teams. I have attached a sample format.

I have to consolidate responses in column D, E and F in Column H. This action needs to be done from row no. 3 to 1000.

Here is the flow chart of the action I want to do with this macro:

For wsSheet1(RowH3:RowH1000)
- Check if cell C3 is having text. If yes, then copy its heading (in Bold with colon) in F3 and then its respective text. In case there is no text ignore and move to next column E.
- Check if column D3 is having text, if yes copy its heading (in Bold with colon) and its respective text in F3 under previous response.
- Perform similar action with E3.

I am not an expert with Macro's so I tried with Record macro. But that doesn't helps because I want to generalize this macro, meaning if I have more than one columns or may be different range of rows, I just change the column and row no. in the macro and use it further.

Please have a look to the attached file. I have tried to work with record macro and results are in column F3. In adjacent column H, I have given the format I desire.

Thanks in advance !!
Your support is appreciated !!


Br,
gmalpani

Would this in F3 do as you need? No VBa required.

=IF(C3="","",TRIM(IF(C3="","",$C$1&CHAR(10)&C3&CHAR(10)&CHAR(10)&IF(D3="","",$D$1&CHAR(10)&D3&CHAR(10)&CHAR(10))&IF(E3="","",$E$1&CHAR(10)&E3))))

Drag/Fill down

Then, if required, Copy and Paste Special > Values and delete any columns that are no longer required

Hi Marcol,

Thanks for the reply !

Actually, I am working on a tool and want this to be done by button click and that's why I was looking for a macro. I need the format as mentioned in column H.
If you can spare some time and help me with a macro that would be a great help.

Anyways this formula works well but I need certain modification in it. I want the headings to be in bold followed by a colon.
e.g. Radio Access:and then its respective text. I have an understanding that this is not possible by using formula.

Thanks for time and support !!

Br,
Gaurav Malpani

You're correct that formatting text can't be done with formula.

Try this, you can use a button to call the sub if you really must.

Sub ConsolidateAndFormat()
    Dim LastRow As Long, RowNo As Long, ChrNo As Long
    Dim strConsolidate As String
    Dim strHdr1 As String, strHdr2 As String, strHdr3 As String
    
    strHdr1 = Cells(1, "C") & ":"
    strHdr2 = Cells(1, "D") & ":"
    strHdr3 = Cells(1, "E") & ":"
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For RowNo = 2 To LastRow
        If Cells(RowNo, "C") = "" Then
            Cells(RowNo, "F") = ""
        Else
            strConsolidate = strHdr1 & Chr(10) & Cells(RowNo, "C")
            If Cells(RowNo, "D") <> "" Then
                strConsolidate = strConsolidate & Chr(10) & Chr(10)
                strConsolidate = strConsolidate & strHdr2 & Chr(10) & Cells(RowNo, "D")
                If Cells(RowNo, "E") <> "" Then
                    strConsolidate = strConsolidate & Chr(10) & Chr(10)
                    strConsolidate = strConsolidate & strHdr3 & Chr(10) & Cells(RowNo, "E")
                End If
            End If
            With Cells(RowNo, "F")
                .Value = strConsolidate
                .Font.Bold = False
                .Characters(1, Len(strHdr1)).Font.Bold = True
                If InStr(Cells(RowNo, "F"), strHdr2) > 0 Then
                    ChrNo = InStr(Cells(RowNo, "F"), strHdr2)
                    .Characters(ChrNo, Len(strHdr2)).Font.Bold = True
                End If
                If InStr(Cells(RowNo, "F"), strHdr3) > 0 Then
                    ChrNo = InStr(Cells(RowNo, "F"), strHdr3)
                    .Characters(ChrNo, Len(strHdr1)).Font.Bold = True
                End If
            End With
        End If
    Next
End Sub