concatenate based on column headers

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
655
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

Posting this after long time :) & Thanks for the help in advance.

I have headers in A1 to Y1 like below
a, b, c, d, "Employee", f,g,h etc...
I want to concatenate values from each row and put in column Z for all rows , I would use formula like =Concatenate(A2,B2,C2,D2), however If the user inserts any column between A to E, then "Employee column will move further. at that time I want to concatenate a,b,c,d,e columns.

In simple words, at any point of time , concatenate should be done for all columns before the column header " Employee"

either formula or Macro works for me :)

Thanks again :) have a nice day ahead.

Regards
Arvind
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
655
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks for the additional information. Since it has to work with 2016 a standard worksheet formula based solution is not feasible.
This user-defined function should do what you want though I believe. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

VBA Code:
Function Conc(rngData As Range, rngHeaders As Range) As String
  Dim i As Long
  Dim FoundNext As Range
 
  Set FoundNext = rngHeaders.Find(What:=i + 1, LookIn:=xlValues, LookAt:=xlWhole)
  Do Until FoundNext Is Nothing
    Conc = Conc & Intersect(rngData, FoundNext.EntireColumn).Value
    i = i + 1
    Set FoundNext = rngHeaders.Find(What:=i + 1, LookIn:=xlValues, LookAt:=xlWhole)
  Loop
End Function

aravindhan_31.xlsm
ABCDEFGHIJKLMAGAH
13214
2
378865863741224995490955096697065516293463655550249463
42499344604739935937959475594361844345450361959993450
54308626491342182671560875318987345776684087360821840
684049366213262871148671210222123617798072712628807
Sheet2
Cell Formulas
RangeFormula
AH3:AH6AH3=Conc(A3:AG3,A$1:AG$1)
Apologies for the delay in reply, Xmas vacation :)

Thanks Peter, This code worked!

however when I drag this down for 500,000 rows, its calculating for each rows and taking more than 3 hours :( I know the formula is like that,
is there any way we can code the formula in VBA and reduce calculation time?

Regards
Arvind
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
however when I drag this down for 500,000 rows, its calculating for each rows and taking more than 3 hours :( I know the formula is like that,
is there any way we can code the formula in VBA and reduce calculation time?
See if this is better. Results are put in Col AH
I have assumed column A can be used to determine the last row of data.

VBA Code:
Sub ConcatValues()
  Dim a As Variant, b As Variant, vRws As Variant, vCols As Variant
  Dim rFound As Range
  Dim i As Long
  Dim sCols As String
  
  i = 1
  Set rFound = Rows(1).Find(What:=i, LookAt:=xlWhole)
  If Not rFound Is Nothing Then
    vRws = Evaluate("Row(3:" & Range("A" & Rows.Count).End(xlUp).Row & ")")
    Do Until rFound Is Nothing
      sCols = sCols & " " & rFound.Column
      i = i + 1
      Set rFound = Rows(1).Find(What:=i, LookAt:=xlWhole)
    Loop
    vCols = Split(Mid(sCols, 2))
    a = Application.Index(Cells, vRws, vCols)
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      b(i, 1) = Join(Application.Index(a, i, 0), "")
    Next i
    With Range("AH3").Resize(UBound(b))
      .NumberFormat = "@"
      .Value = b
    End With
  End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,431
Messages
5,624,753
Members
416,046
Latest member
Elliottj2121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top