Concatenate Sheet into 1 cell?

[brolsen]

Hello All,

Just need help either creating a code or a formula that I am unaware of that will allow you to concatenate information from two columns (with several rows) into 1 cell.

Attached is a mock workup of an excel with 3 sheets (Main, AAFB, AAB). The "real one" I am using had private data, but the one I have attached is a smaller version with names changed and data changed.

If you look at the "Main" sheet, You will see 2 cells. What I am trying to do is get a code that is a command or something that I can do to get the info in the many columns of AAFB into cell A2 with carriage returns CHAR(10) between rows and the same for the one below it AAB.

Hope someone knows how to do this?

(Also, I need the command to only concatenate cells in the different sheets (ie AAFB & AAB) dynamically. So if the number of values changes, the concatenate feature will only copy over the rows/columns that aren't blank if it is shorter and will grab the new additions if it is longer.)

[Marcol]

Why would you want to do this?

However, try this UDF
In a standard VBa module

Option Explicit

Function Conc(rng As Range, Optional Separator As String = "")
    Dim Cell As Range
        
    For Each Cell In rng
        If Cell <> "" Then
            Conc = Conc & Separator & Cell
        End If
    Next
    If Separator <> "" Then
        Conc = Right(Conc, Len(Conc) - Len(Separator))
    End If
End Function

Then in A2

=conc(OFFSET(INDIRECT(A1&"!$A$2"),0,0,COUNTA(INDIRECT(A1&"!$A:$A"))-1,1),CHAR(10))

And in A4

=conc(OFFSET(INDIRECT(A3&"!$A$2"),0,0,COUNTA(INDIRECT(A3&"!$A:$A"))-1,1),CHAR(10))

Remember to wrap the text in A2 & A4

Hope this might help.