concatenate based on column headers

[aravindhan_31]

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

 

[Peter_SSs]

@Dossfm0q
Please review #4 of the Forum Rules, particularly the last two paragraphs.

 

[aravindhan_31]

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.

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
	A	B	C	D	E	F	G	H	I	J	K	L	M																				AG	AH
1					3			2			1																						4
2
3	788	658	637	412	249	954	909	550	966	970	655	16	293																				463	655550249463
4	249	934	460	473	993	59	37	959	475	594	361	844	345																				450	361959993450
5	430	862	649	134	21	826	715	608	753	189	873	457	766																				840	87360821840
6	840	493	662	132	628	711	486	712	102	221	2	361	779																				807	2712628807
Sheet2

Cell Formulas
Range	Formula
AH3:AH6	AH3	=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

 

[aravindhan_31]

Click SeeFile
Data in hidden columns

Hi Doss,

Thanks for your help:

I Am not able to find the file

 

[Peter_SSs]

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.

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