concatenate without looping - VBA

[ericlch16]

Hello

I have two column(A,B). 3rd column(C) is to concatenate column 1 &"-" & column 2 e.g

aaa bbb aaa-bbb
cc ww cc-ww

I remember there is a quick way to do column C without looping. The rows are in the thousands and I am trying to avoid looping.

.range(.cells(1,3), cells(lastrow, 3)) = ???

Thanks,

 

[Joe4]

All rows 1,000,000 took:
Not looping: 4.78
Looping: 4.32

I cannot reproduce that result.
I tried it again, two tests again, comparing my VBA to the loop VBA code, on 1,000,000 rows of data.

Here are my results:
My code: 1.16 seconds and 1.04 seconds
Loop code: 6.28 seconds and 5.73 seconds

Even if you get your results, and not mine, the questions you then would need to ask yourself are the following:
1. How many rows might you actually have?
2. Which code are you more comfortable supporting (do you understand how both codes work)?
3. Can you live with a 0.5 second delay using the "slower" code for 1 million records?

For me, if the difference is neglible, I will usually opt for the simpler code, so it can more easily be supported (by myself or whoever else might take it over from me).

 

[trunten]

Thanks. that's what i originally thought. From previous recommendation on this forum, i changed the way i code. I tried to avoid looping as much as possible because i have to deal with so many rows of data.

Thanks for your expertise once more. (your username is very familiar to me and i think I learned a lot from your previous posts)

Strange, n my machine you're code is slower by a large margin every time. Looping wins.

Option Explicit

Sub concatit()
    Dim t, a, b, i&, lastRow&
    t = Timer
    'lastRow = Range("A:B").Find("*", Range("A1"), xlValues, 2, 1, 2, False).Row
    With Range("C1:C1000000")
        t = Timer
        .FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        .Value = .Value
        Debug.Print "Not looping 1: " & Round(Timer - t, 2) & " seconds"
        'erichlch16'
        t = Timer
        Range("C1").FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        Range("C1").AutoFill Destination:=Range("C1:C1000000"), Type:=xlFillDefault
        .Value = .Value
        '''
        Debug.Print "Not looping 2: " & Round(Timer - t, 2) & " seconds"
        t = Timer
        a = .Offset(, -2).Resize(, 2).Value
        ReDim b(1 To UBound(a), 1 To 1)
        For i = 1 To UBound(a): b(i, 1) = a(i, 1) & "-" & a(i, 2): Next i
        .Value = b
        Debug.Print "Looping: " & Round(Timer - t, 2) & " seconds"
        Erase a
        Erase b
    End With
End Sub

Not looping 1: 2.75 seconds
Not looping 2: 5.66 seconds
Looping: 2.16 seconds

 

[Joe4]

Strange, n my machine you're code is slower by a large margin every time. Looping wins.

Not looping 1: 2.75 seconds
Not looping 2: 5.66 seconds
Looping: 2.16 seconds

What exactly do you have in column A and column B?
Do you have any other data or any other code?
How exactly are you timing the code (I used the first method shown here: https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time)?

 

[Fluff]

Strange, n my machine you're code is slower by a large margin every time. Looping wins.

You are converting the formula into values, if you take that out then on two runs I get
Not looping 1: 0.96 seconds
Not looping 2: 2.49 seconds
Looping: 3.27 seconds
Not looping 1: 0.98 seconds
Not looping 2: 2.5 seconds
Looping: 3.28 seconds

 

[trunten]

MY mistake, I figured we needed the resulting value. If we don't need the resulting value rather than a formula I'd say don't use VBA at all.

 

[Joe4]

MY mistake, I figured we needed the resulting value. If we don't need the resulting value rather than a formula I'd say don't use VBA at all.

From the question asked, I believe the issue is that they are trying to automate it, and there is a dynamic number of rows.
Hence, the need for VBA.

 

[trunten]

I suppose. My preference would probably be a table with a calculated column tbh. To each their own as they say

Just as a nice little curiosity, text join seems to win on my machine

Not looping 1: 0.88 seconds
Not looping 2: 1.03 seconds

Sub concatit()
    Dim t, lastRow&
    
    ''''''''''''''''''''''
    'Dummy data for test
    ''''''''''''''''''''''
    Range("A1:A1000000").Value = "aaa"
    Range("B1:B1000000").Value = "bbb"
    ''''''''''''''''''''''
    
    t = Timer
    lastRow = Range("A:C").Find("*", Range("A1"), xlValues, 2, 1, 2, False).Row
    With Range("C1", Cells(lastRow, "C"))
        t = Timer
        .Formula = "=TEXTJOIN(""-"",FALSE,A1:B1)"
        '.Value = .Value
        Debug.Print "Not looping 1: " & Round(Timer - t, 2) & " seconds"
        t = Timer
        .FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        '.Value = .Value
        Debug.Print "Not looping 2: " & Round(Timer - t, 2) & " seconds"
    End With
End Sub

 

[Joe4]

Quote from private message:

i tried to implement your code for

Range(Cells(1, 3), Cells(lastrow, 3)).FormulaR1C1 = "=RC[-2] & ""-"" & RC[-1]"

Instead of put -2 or -1, is there a way to put it as a variable of the actual column?? I have so many columns and i keep these variables as column number.

Note that with R1C1 notation, you can use either ABSOLUTE or RELATIVE reference.
See here for a complete explanation with examples: An Ultimate Guide To Learn R1C1 Reference Style in Excel