CONCATENATE

[Sufiyan97]

I want to =CONCATENATE 'Column I' occurring of next number in 'Column H' as per attached picture expected result in 'Column J'

 

[Bo_Ry]

With helper column K

Book1
	H	I	J	K
1	1	A	A B C D E F	A B C D E F
2		B		B C D E F
3		C		C D E F
4		D		D E F
5		E		E F
6		F		F
7	2	A	A B C D E	A B C D E
8		B		B C D E
9		C		C D E
10		D		D E
11		E		E
12	3	A	A B C D	A B C D
13		B		B C D
14		C		C D
15		D		D
16	4	A	A B C	A B C
17		B		B C
18		C		C
19	5	A	A B	A B
20		B		B
21	6	A	A	A
Sheet1

Cell Formulas
Range	Formula
J1:J21	J1	=IF(H1,K1,"")
K1:K21	K1	=IF(H2,I1,I1&" "&K2)

 

[Sufiyan97]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use. Thanks

Thanks for your suggestion, as I am new here I did not know that.

Now Updated.

 

[Sufiyan97]

With helper column K

Book1
	H	I	J	K
1	1	A	A B C D E F	A B C D E F
2		B		B C D E F
3		C		C D E F
4		D		D E F
5		E		E F
6		F		F
7	2	A	A B C D E	A B C D E
8		B		B C D E
9		C		C D E
10		D		D E
11		E		E
12	3	A	A B C D	A B C D
13		B		B C D
14		C		C D
15		D		D
16	4	A	A B C	A B C
17		B		B C
18		C		C
19	5	A	A B	A B
20		B		B
21	6	A	A	A
Sheet1

Cell Formulas
Range	Formula
J1:J21	J1	=IF(H1,K1,"")
K1:K21	K1	=IF(H2,I1,I1&" "&K2)

Thank you very much!
This works.

Thank you very much again.

 

[CA_Punit]

So if you can manually concatenate with few Helper column and if the data range for concatenate is few then you can try But Bo_Ry formula is worth applying

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H	I
1	1	A	ABCDE	A	B	C	D	E
2		B
3		C
4		D
5		E
6	2	A	ABCDEF	A	B	C	D	E	F
7		B
8		C
9		D
10		E
11		F
12	3	A	ABCD	A	B	C	D
13		B
14		C
15		D
16	4	A	ABC	A	B	C
17		B
18		C
19
20
Sheet2

Cell Formulas
Range	Formula
C1,C16,C12,C6	C1	=CONCATENATE(D1,E1,F1,G1,H1,I1)
D1:H1,D12:G12,D6:I6	D1	=IF(A1="","",TRANSPOSE(OFFSET(B1,,,AGGREGATE(15,6,IFERROR(1/(1/(((A2:$A$100)<>"")*ROW(A2:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(#REF!))-ROW()+1),1))))
D16:F16	D16	=IF(A16="","",TRANSPOSE(OFFSET(B16,,,AGGREGATE(15,6,IFERROR(1/(1/(((A17:$A$100)<>"")*ROW(A17:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(A1:A100))-ROW()+1),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sufiyan97]

So if you can manually concatenate with few Helper column and if the data range for concatenate is few then you can try But Bo_Ry formula is worth applying

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H	I
1	1	A	ABCDE	A	B	C	D	E
2		B
3		C
4		D
5		E
6	2	A	ABCDEF	A	B	C	D	E	F
7		B
8		C
9		D
10		E
11		F
12	3	A	ABCD	A	B	C	D
13		B
14		C
15		D
16	4	A	ABC	A	B	C
17		B
18		C
19
20
Sheet2

Cell Formulas
Range	Formula
C1,C16,C12,C6	C1	=CONCATENATE(D1,E1,F1,G1,H1,I1)
D1:H1,D12:G12,D6:I6	D1	=IF(A1="","",TRANSPOSE(OFFSET(B1,,,AGGREGATE(15,6,IFERROR(1/(1/(((A2:$A$100)<>"")*ROW(A2:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(#REF!))-ROW()+1),1))))
D16:F16	D16	=IF(A16="","",TRANSPOSE(OFFSET(B16,,,AGGREGATE(15,6,IFERROR(1/(1/(((A17:$A$100)<>"")*ROW(A17:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(A1:A100))-ROW()+1),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your help! and Yes Bo_Ry formula is easier and worthy.