Concat several columns using column names in Excel VBA

Question

As shown in image_1, I have the raw data of a product as shown in Column B to Column F. I want to add column A, which concats the "Model", "Year", "Number" data into a string. I know I can achieve this simply by [a2] = "=concat(B2,D2,F2)", and then filldown. But the problem is that the raw file I receive every day is inconsistent in terms of the order of the columns. Therefore, I couldn't use a static line of code displayed above.

I can probably use a combination of for loop and if/else to test if the column name equal to "Model", "Year", "Number", and if yes, grab its column number...

However, I'm wondering if there's a more direct and elegant way of achieving this. Any thoughts?

I would create a loop like you suggested - similar to the [function in my answer here](https://stackoverflow.com/a/67334698/9663006) but including the aforementioned loop with if/else statements. — Samuel Everson, May 04 '21 at 03:04
If using VBA you could convert to a list object and reference by column name, or on a spreadsheet convert to a table and reference by column name - `=CONCAT(MyDataTable[Model],MyDataTable[Color],MyDataTable[Year])` — Darren Bartrup-Cook, May 04 '21 at 14:03
how do i convert that to a list object tho? @DarrenBartrup-Cook — JackeyOL, May 05 '21 at 13:53

score 1 · Answer 1 · answered May 04 '21 at 04:11

A simple approach based on sorting columns by header and merging data in columns with constant numbers (assuming constant number of columns but different order). If the number of columns is variable, this code will not work.

Sub concat()
    Dim rng As Range
    
    With ThisWorkbook.Worksheets(1)
        Set rng = .Range("A1").CurrentRegion
        ' columns sort
        With .Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=rng.Rows(1)
            .SetRange rng
            .Orientation = xlLeftToRight
            .Apply
        End With
        Set rng = rng.Columns(1)
        rng.Insert  ' add cells at left for "Concat"
        Set rng = rng.Offset(0, -1)
        rng(1) = "Concat"   ' add header
        Intersect(rng, rng.Offset(1)).FormulaR1C1 = "=CONCAT(RC[2],RC[5],RC[3])"
    End With
End Sub

Before

After

Rob Kellock · Answer 2 · 2021-05-04T04:32:37.900

If they are always the same columns, just the order changes then sort by column headings first before concatenating, that way they will always be in the same position.

If you have differing columns and the ones you are interested in are somewhere within it, then you could use the following formula: =HLOOKUP("Heading_Name","Data_Range",Row_No,FALSE) to extract each of the columns you are interested in. Concatenating the results of these would give you what you want and will work for any arrangement of columns and sizes of data providing you declare the range properly.

score 0 · Accepted Answer · answered May 04 '21 at 05:03

If you want to add the "Concat" column and formula without reordering the columns, you can do that with vba like this

Sub Demo()
    Dim ws As Worksheet
    Dim colModel As Variant
    Dim colYear As Variant
    Dim colNum As Variant
    Dim LastRow As Long
    
    Set ws = ActiveSheet ' or any means you choose
    
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    If ws.Cells(1, 1) <> "Concat" Then
        'Insert new column
        ws.Columns(1).Insert
        
        ' New column header
        ws.Cells(1, 1) = "Concat"
    End If
    
    ' get colum positions
    colModel = Application.Match("Model", ws.Rows(1), 0)
    colYear = Application.Match("Year", ws.Rows(1), 0)
    colNum = Application.Match("Number", ws.Rows(1), 0)
    
    ' Check if columns exist
    If IsError(colModel) Then
        MsgBox "Column ""Model"" not found", vbCritical + vbOKOnly, "Error"
        Exit Sub
    End If
    If IsError(colYear) Then
        MsgBox "Column ""Year"" not found", vbCritical + vbOKOnly, "Error"
        Exit Sub
    End If
    If IsError(colNum) Then
        MsgBox "Column ""Number"" not found", vbCritical + vbOKOnly, "Error"
        Exit Sub
    End If
    
    ' Insert Formula
    ws.Range(ws.Cells(2, 1), ws.Cells(LastRow, 1)).FormulaR1C1 = "=RC[" & colModel - 1 & "]&RC[" & colYear - 1 & "]&RC[" & colNum - 1 & "]"
    
End Sub

Alternatively, you could also use a formula in column A to find the column positions

In Excel365

=XLOOKUP("Model",$1:$1,2:2,,0)&XLOOKUP("Year",$1:$1,2:2,,0)&XLOOKUP("Number",$1:$1,2:2,,0)

For pre 365

=INDEX(2:2,MATCH("Model",$1:$1,0))&INDEX(2:2,MATCH("Year",$1:$1,0))&INDEX(2:2,MATCH("Number",$1:$1,0))

Concat several columns using column names in Excel VBA

3 Answers3