SOLVED
Home

FORMULA TO MERGE ALL CELL ITEM IN ABC COLUMN TO D

%3CLINGO-SUB%20id%3D%22lingo-sub-853806%22%20slang%3D%22en-US%22%3EFORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853806%22%20slang%3D%22en-US%22%3E%3CP%3EWHAT%20IS%20the%26nbsp%3BFORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131617i659DB08761141981%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-853806%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853815%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853815%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20Concatenate()%20formula%20to%20merge%20text%20in%20different%20columns.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCONCATENATE(A1%2CB1%2CC1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFile%20is%20attached%20for%20your%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853820%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3Eneed%20result%20like%20below%20using%20formula%20(not%20copy%20paste%20as%20we%20have%20thousands%20of%20columns%20that%20need%20to%20merge)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20945px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131619i0D3C3DCB9A02B26B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853850%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20goal%20is%20only%20to%20combine%2Fappend%20them%20into%20one%20column%2C%20the%20best%20solution%20is%20Get%20%26amp%3B%20Transform%20(aka%20Power%20Query).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131636i3513CC6CBB1F7CDB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-13_8-34-14.png%22%20title%3D%222019-09-13_8-34-14.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20steps%20are%20explained%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DKWa7snKsLz0%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Evideo%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853880%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20open%20to%20a%20VBA%20Solution%2C%20please%20find%20the%20attached%20and%20click%20the%20button%20called%20%22Merge%20Multiple%20Columns%20Into%20One%22%20on%20Sheet1%20to%20merge%20all%20the%20columns%20with%20data%20on%20Sheet1%20and%20get%20the%20data%20in%20a%20single%20column%20in%20the%20next%20empty%20column.%20e.g.%20if%20the%20sheet%20has%203%20columns%2C%20the%20code%20will%20combine%20all%20the%20data%20and%20place%20the%20output%20in%204th%20column%20i.e.%20column%20D.%20Similarly%20if%20the%20sheet%20has%2010%20columns%2C%20the%20code%20will%20combine%20all%20the%20data%20and%20place%20the%20output%20in%2011th%20column%20i.e.%20column%20K.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20MergeMultipleColumnsIntoOne()%0ADim%20lr%20%20As%20Long%0ADim%20lc%20%20As%20Long%0ADim%20i%20%20%20As%20Long%0ADim%20j%20%20%20As%20Long%0ADim%20k%20%20%20As%20Long%0ADim%20x%20%20%20As%20Variant%0ADim%20y()%20As%20Variant%0A%0Alr%20%3D%20Cells.Find(%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0Alc%20%3D%20Cells(1%2C%20Columns.Count).End(xlToLeft).Column%0A%0Ax%20%3D%20Range(Cells(1%2C%201)%2C%20Cells(lr%2C%20lc)).Value%0AReDim%20y(1%20To%20UBound(x%2C%201)%20*%20UBound(x%2C%202)%2C%201%20To%201)%0A%0AFor%20j%20%3D%201%20To%20UBound(x%2C%202)%0A%20%20%20%20For%20i%20%3D%201%20To%20UBound(x%2C%201)%0A%20%20%20%20%20%20%20%20If%20x(i%2C%20j)%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20k%20%3D%20k%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20y(k%2C%201)%20%3D%20x(i%2C%20j)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0ANext%20j%0ACells(1%2C%20lc%20%2B%201).Resize(k%2C%201).Value%20%3D%20y%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855223%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855223%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks!%20vba%20worked%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855260%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MERGE%20ALL%20CELL%20ITEM%20IN%20ABC%20COLUMN%20TO%20D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855260%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Marvin Oco
Super Contributor

WHAT IS the FORMULA TO MERGE ALL CELL ITEM IN ABC COLUMN TO D?

 

please see attached

 

Capture.JPG

6 Replies

Hi @Marvin Oco 

 

You can use Concatenate() formula to merge text in different columns.

=CONCATENATE(A1,B1,C1)

File is attached for your reference.

@tauqeeracmaneed result like below using formula (not copy paste as we have thousands of columns that need to merge)

 

Capture.JPG

@Marvin Oco

 

Hi,

 

If your goal is only to combine/append them into one column, the best solution is Get & Transform (aka Power Query).

2019-09-13_8-34-14.png

 

The solution steps are explained in this video.

 

Regards

Solution

@Marvin Oco 

If you are open to a VBA Solution, please find the attached and click the button called "Merge Multiple Columns Into One" on Sheet1 to merge all the columns with data on Sheet1 and get the data in a single column in the next empty column. e.g. if the sheet has 3 columns, the code will combine all the data and place the output in 4th column i.e. column D. Similarly if the sheet has 10 columns, the code will combine all the data and place the output in 11th column i.e. column K.

 

Sub MergeMultipleColumnsIntoOne()
Dim lr  As Long
Dim lc  As Long
Dim i   As Long
Dim j   As Long
Dim k   As Long
Dim x   As Variant
Dim y() As Variant

lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column

x = Range(Cells(1, 1), Cells(lr, lc)).Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 1)

For j = 1 To UBound(x, 2)
    For i = 1 To UBound(x, 1)
        If x(i, j) <> "" Then
            k = k + 1
            y(k, 1) = x(i, j)
        End If
    Next i
Next j
Cells(1, lc + 1).Resize(k, 1).Value = y
End Sub

 

thanks! vba worked 

You're welcome @Marvin Oco! Glad it worked as desired.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies