CODEWITHSUNDEEP
vbaexcelconcatenation
Lakshmi KrishnaKumaar
Lakshmi KrishnaKumaar

Reputation: 39

How concatenate columns in excel repeatedly

I have 256 columns in a excel sheet. I need to bring them to 128 columns by merging pairs of columns. How can I repeat the process of concatenating two cells throughout the sheet

Eg:

Col | Col2  | Col3 | Col4

1:  | 45    |  2:  |  556

1:  | 34    |  2:  |  567

Now my expected result is

Col1 | Col2

1:45 | 2:556

1:34 | 2:567

How can I perform this in Excel?

Can I use VBA?

Upvotes: 0

Views: 417

Answers (3)

Tom
Tom

Reputation: 9878

Using the formula (in cell A4 in image below)

=OFFSET(A1, , COLUMN()-1)&OFFSET(B1,,COLUMN()-1)

Example

Upvotes: 1

JohnyL
JohnyL

Reputation: 7122

Assuming that range is "A1:IV1000" (change to what you need). In the end the original range is cleared and resulting array is dumped.

Sub ConcatColumns()

    Dim rng As Range
    Dim x As Long, c As Integer, z As Integer
    Dim arr, arrResult
    Const COLS As Integer = 256

    Set rng = Range("A1:IV100") '//256 columns
    arr = rng.Value
    ReDim arrResult(1 To UBound(arr, 1), 1 To COLS / 2)
    For x = 1 To UBound(arr, 1)
        z = 1
        For c = 1 To COLS - 1 Step 2
            arrResult(x, z) = arr(x, c) & arr(x, c + 1)
            z = z + 1
        Next
    Next

    rng.ClearContents '//Clear all previous data
    Range("A1").Resize(UBound(arrResult, 1), UBound(arrResult, 2)).Value = arrResult '//Dump result

End Sub

Upvotes: 2

jkpieterse
jkpieterse

Reputation: 2956

Add two columns with the simple formula =A2&B2. Copy down. then copy resulting formula columns and paste special values to get rid of formula.

Upvotes: 2

Related Questions