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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
either formula or Macro works for me
To consider a formula approach, it would be helpful to know your Excel version. So I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

Another help would be to give us a small set of sample data and the expected results with XL2BB
 

aravindhan_31

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

I agree, should have been more clearer

Here is the image , Based on the order number given in Row 1, the range has to be concatenated
in this example, K, H, E & M in the order each rows have to concatenated. In case I change the order in row 1, then again concatenate formula should take that way.


Sample Data.JPG


Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your profile. (y)

Here is the image ,
I cannot copy from an image, hence my request for you to use XL2BB. ;)
It will generally get you faster suggestions and directly aligned to your data.

Use this with Excel 365

aravindhan_31.xlsm
ABCDEFGHIJKLMNOP
13214
2
37886586374122499549095509669706551629346366655550249293
42499344604739935937959475594361844345450226361959993345
54308626491342182671560875318987345776684093087360821766
684049366213262871148671210222123617798073282712628779
Sheet1
Cell Formulas
RangeFormula
P3:P6P3=CONCAT(INDEX(SORTBY(A3:O3,A$1:O$1),SEQUENCE(COUNT(A$1:O$1))))
 

aravindhan_31

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

ADVERTISEMENT

Thanks Peter,
I have to share the file with other collegues who do not have 365 and uses 2016, can you help me with the formula or VBA for that please

Regards
Arvind
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
To decide the best approach, more details of what could occur in the sheet would help.
  1. Will the data only ever go to column O or could go further/less?
  2. Which is the first column that could have a digit in row 1?
  3. Will row 1 always contain exactly the digits 1 - 4? .. or
    • Could it be 1 - 8 (more digits) or just 1-3 (less digits)
    • Could it be 1,2,4,5 (3 is missing)
    • Could it be 1,2,2,3,4 (2 is duplicated)
    • Could it be 3,4,5,6 (not starting at 1)
 

aravindhan_31

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

ADVERTISEMENT

To decide the best approach, more details of what could occur in the sheet would help.
  1. Will the data only ever go to column O or could go further/less? column A to AG & formula should be there in Col
  2. Which is the first column that could have a digit in row 1? Column A
  3. Will row 1 always contain exactly the digits 1 - 4? .. or will have numbers anywhere between 1 to 33
    • Could it be 1 - 8 (more digits) or just 1-3 (less digits) -will have numbers anywhere between 1 to 33
    • Could it be 1,2,4,5 (3 is missing) - No
    • Could it be 1,2,2,3,4 (2 is duplicated) - No
    • Could it be 3,4,5,6 (not starting at 1)- No
For all the headers I will type manually which one to come first , 2nd, 3rd etc. so the numbers I will enter 1, 2,3,4... 33 can be anywhere between 1to 33 & my concatenate formula should take in ascending order, if I re type the sequence of columns.

1608720215040.png

Regards
Arvind
Similar post: concatenate based on User input
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Excel 2019
Greetings All
Mr: Peter only By Pressing F9
Excel Formula:
INDIRECT(ADDRESS(ROW(),AGGREGATE(15,6,(COLUMN($B$1:$O$1)+COLUMN($B$1)-1)/($B$1:$O$1<>""),TRANSPOSE(ROW(INDIRECT("1:"&COUNTA($B$1:$O$1)))))))
inside CONCAT showing desired result "CONCAT({"ABC111","JFG1","Test3","Test6","Test9","Test11"})" = "ABC111JFG1Test3Test6Test9Test11" as First Row

With your experience and knowledge. Your help is highly appreciated

Book1
ABCDEFGHIJKLMNOP
1513426
2ABCDEFGHIJKLMNOCONCATENATE
31ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111JFG1Test3Test6Test9Test11
42ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
53ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111
64ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
75ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111
86ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
97ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111
108ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
119ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111
1210ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
1311ActiveABC111JFG1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC111
1412ActiveABC222JFG2Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12ABC222
Sheet1
Cell Formulas
RangeFormula
A2:O2B2=SUBSTITUTE(ADDRESS(2,COLUMN(),4),ROW(),"")
A3:A14A3=ROWS($B$3:B3)
P3P3=CONCAT({"ABC111","JFG1","Test3","Test6","Test9","Test11"})
P4:P14P4=CONCAT(INDIRECT(ADDRESS(ROW(),AGGREGATE(15,6,(COLUMN($B$1:$O$1)+COLUMN($B$1)-1)/($B$1:$O$1<>""),TRANSPOSE(ROW(INDIRECT("1:"&COUNTA($B$1:$O$1))))))))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,022
Messages
5,628,186
Members
416,299
Latest member
arunvistas

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