formula to join text in multiple rows at each date.

[RAJESH1960]

Hello experts
I have 2019 verion of excel. I am trying to join the text in column D which is spread in 2 or more rows in the row of each date. I have inserted a help column E and I used “&” function but it works only when the text in column D has 2 rows only.If I use this formula =IF(C1="","",D1&” “&D2), the rows containing text in 3 or more rows is not copied. The sheet has more than 800 different dates. So, it will be tedious to change the formula at every 3 or 4 rows. Need help with a formula to join the text if the rows vary.

Join text different rows.xlsx
	A	B	C	D	E
1			17-08-2021	aaz/116820829672/a2V/yzavz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz
2				zzzyz555@ayazz/yzyazazzz
3			17-08-2021	aaz/116820329131/a2V/azzyz	aaz/116820329131/a2V/azzyzaw16889-
4				aw16889-
5				1@ayzxza/azzyzaW
6			17-08-2021	yzza_zy:yyzyz2116881584	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa
7				3/0031/ yzazaz zyazazaa
8			16-08-2021	yzza_aaa:aayzz21167213	yzza_aaa:aayzz21167213021/azyzy
9				021/azyzy
10				zyyavzazaya/yyzy0001435/04
11				13090674
12			16-08-2021	zyaa-	zyaa-zy/116713428622/924626717
13				zy/116713428622/924626717
14				1/yzazzazy
Bank

Cell Formulas
Range	Formula
E1:E14	E1	=IF(C1="","",D1&D2)

 

[Eric W]

This should work if you have at most 10 rows to concatenate:

Book1
	A	B	C	D	E
1			8/17/2021	aaz/116820829672/a2V/yzavz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz
2				zzzyz555@ayazz/yzyazazzz
3			8/17/2021	aaz/116820329131/a2V/azzyz	aaz/116820329131/a2V/azzyzaw16889-1@ayzxza/azzyzaW
4				aw16889-
5				1@ayzxza/azzyzaW
6			8/17/2021	yzza_zy:yyzyz2116881584	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa
7				3/0031/ yzazaz zyazazaa
8			8/16/2021	yzza_aaa:aayzz21167213	yzza_aaa:aayzz21167213021/azyzyzyyavzazaya/yyzy0001435/0413090674
9				021/azyzy
10				zyyavzazaya/yyzy0001435/04
11				13090674
12			8/16/2021	zyaa-	zyaa-zy/116713428622/9246267171/yzazzazy
13				zy/116713428622/924626717
14				1/yzazzazy
15
Sheet12

Cell Formulas
Range	Formula
E1:E14	E1	=IF(C1="","",CONCAT(D1:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))

 

[jasonb75]

This looks like it works as required using an additional helper column.

Book1
	C	D	E	F
1	17/08/2021	aaz/116820829672/a2V/yzavz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz
2		zzzyz555@ayazz/yzyazazzz	zzzyz555@ayazz/yzyazazzz
3	17/08/2021	aaz/116820329131/a2V/azzyz	aaz/116820329131/a2V/azzyzaw16889-1@ayzxza/azzyzaW	aaz/116820329131/a2V/azzyzaw16889-1@ayzxza/azzyzaW
4		aw16889-	aw16889-1@ayzxza/azzyzaW
5		1@ayzxza/azzyzaW	1@ayzxza/azzyzaW
6	17/08/2021	yzza_zy:yyzyz2116881584	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa
7		3/0031/ yzazaz zyazazaa	3/0031/ yzazaz zyazazaa
8	16/08/2021	yzza_aaa:aayzz21167213	yzza_aaa:aayzz21167213021/azyzyzyyavzazaya/yyzy0001435/0413090674	yzza_aaa:aayzz21167213021/azyzyzyyavzazaya/yyzy0001435/0413090674
9		021/azyzy	021/azyzyzyyavzazaya/yyzy0001435/0413090674
10		zyyavzazaya/yyzy0001435/04	zyyavzazaya/yyzy0001435/0413090674
11		13090674	13090674
12	16/08/2021	zyaa-	zyaa-zy/116713428622/9246267171/yzazzazy	zyaa-zy/116713428622/9246267171/yzazzazy
13		zy/116713428622/924626717	zy/116713428622/9246267171/yzazzazy
14		1/yzazzazy	1/yzazzazy
Sheet1

Cell Formulas
Range	Formula
E1:E14	E1	=D1&IF(C2="",E2,"")
F1:F14	F1	=IF(C1="","",E1)

 

[RAJESH1960]

This should work if you have at most 10 rows to concatenate:

Book1
	A	B	C	D	E
1			8/17/2021	aaz/116820829672/a2V/yzavz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz
2				zzzyz555@ayazz/yzyazazzz
3			8/17/2021	aaz/116820329131/a2V/azzyz	aaz/116820329131/a2V/azzyzaw16889-1@ayzxza/azzyzaW
4				aw16889-
5				1@ayzxza/azzyzaW
6			8/17/2021	yzza_zy:yyzyz2116881584	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa
7				3/0031/ yzazaz zyazazaa
8			8/16/2021	yzza_aaa:aayzz21167213	yzza_aaa:aayzz21167213021/azyzyzyyavzazaya/yyzy0001435/0413090674
9				021/azyzy
10				zyyavzazaya/yyzy0001435/04
11				13090674
12			8/16/2021	zyaa-	zyaa-zy/116713428622/9246267171/yzazzazy
13				zy/116713428622/924626717
14				1/yzazzazy
15
Sheet12

Cell Formulas
Range	Formula
E1:E14	E1	=IF(C1="","",CONCAT(D1:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))

This should work if you have at most 10 rows to concatenate:

Book1
	A	B	C	D	E
1			8/17/2021	aaz/116820829672/a2V/yzavz	aaz/116820829672/a2V/yzavzzzzyz555@ayazz/yzyazazzz
2				zzzyz555@ayazz/yzyazazzz
3			8/17/2021	aaz/116820329131/a2V/azzyz	aaz/116820329131/a2V/azzyzaw16889-1@ayzxza/azzyzaW
4				aw16889-
5				1@ayzxza/azzyzaW
6			8/17/2021	yzza_zy:yyzyz2116881584	yzza_zy:yyzyz21168815843/0031/ yzazaz zyazazaa
7				3/0031/ yzazaz zyazazaa
8			8/16/2021	yzza_aaa:aayzz21167213	yzza_aaa:aayzz21167213021/azyzyzyyavzazaya/yyzy0001435/0413090674
9				021/azyzy
10				zyyavzazaya/yyzy0001435/04
11				13090674
12			8/16/2021	zyaa-	zyaa-zy/116713428622/9246267171/yzazzazy
13				zy/116713428622/924626717
14				1/yzazzazy
15
Sheet12

Cell Formulas
Range	Formula
E1:E14	E1	=IF(C1="","",CONCAT(D1:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))

Ii worked perfectly. Now I have to just change the range to 1000 and check. Thanks Eric W.?

 

[Eric W]

You really don't need to change the range unless you expect to have more than 10 rows to concatenate. Those ranges are designed to look ahead just 10 rows. Even if the total range is 1000, the lookahead range can still be 10.

Anyway, glad we could help!