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]

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]

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.

Thank you

 

[Peter_SSs]

Thanks for updating your profile.

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

Cell Formulas
Range	Formula
P3:P6	P3	=CONCAT(INDEX(SORTBY(A3:O3,A$1:O$1),SEQUENCE(COUNT(A$1:O$1))))

 

[aravindhan_31]

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]

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]

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.

Regards
Arvind
Similar post: concatenate based on User input

 

[Dossfm0q]

Excel 2019
Greetings All
Mr: Peter only By Pressing F9

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		5	1		3			4			2		6
2	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	CONCATENATE
3	1	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111JFG1Test3Test6Test9Test11
4	2	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
5	3	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111
6	4	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
7	5	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111
8	6	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
9	7	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111
10	8	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
11	9	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111
12	10	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
13	11	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC111
14	12	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	ABC222
Sheet1

Cell Formulas
Range	Formula
A2:O2	B2	=SUBSTITUTE(ADDRESS(2,COLUMN(),4),ROW(),"")
A3:A14	A3	=ROWS($B$3:B3)
P3	P3	=CONCAT({"ABC111","JFG1","Test3","Test6","Test9","Test11"})
P4:P14	P4	=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]

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)

 

[Dossfm0q]

Click SeeFile
Data in hidden columns