Variable number of columns to concatenate dynamically

[pcsrvc]

Hi all!

As you can see I am very new to the message board and have been studying Excel and VBA very hard and hopefully one day I will be able to pay it forward and help people out here as well. While I get my skills up to the task I would love it if you could help me out with something that might be very simple for you but I have not been successful finding a solution after researching for at least a week.

This is my problem:

I have a varying number of columns containing strings of text. I need to concatenate the middle strings. The position where the joining starts varies based on the value of a cell on the same row. First and last strings are ignored.

Example: Columns D - H, row 2: Sir John Wick Warhurst Taylor

I need to ignore "Sir" and "John", concatenate "Wick Warhurst", ignore Taylor. After that I will have to copy John, Wick Warhurst and Taylor to 3 different columns on the same row.

The number of strings can be anything up to 10. The result of the concatenation will always be 3 separate strings no matter how many strings are in the middle.

Can anyone please help? I will certainly study any codes you can come up with to learn how you did it and improve my skills.

Thank you in advance, you all are the best!

 

[Sufiyan97]

Post some rows of data as an example using below link.

 

[pcsrvc]

Does the below help?

The Starting row column states what column the concatenation should start. Particle Test column states whether the string on column 4 should be added to the last string resulting the same as the first row of date on the table below.

Starting row	Particle Test	1	2	3	4	5	6	FIRST	MIDDLE	LAST
2	TRUE	Avenida	Juscelino	Kubitschek	De	Oliveira		Juscelino	Kubitschek	De Oliveira
2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364/br-163
2	FALSE	Estrada	Francisco	Da	Cruz	Nunes
2	TRUE	Avenida	Ayrton	Senna	Da	Silva
2	TRUE	Rua	Cláudio	Manoel	Da	Costa
2	FALSE	Rodovia	Ernâni	Do	Amaral	Peixoto/rj-106
2	TRUE	Avenida	Raimundo	Pereira	De	Magalhães
2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364
2	TRUE	Rua	Juscelino	Kubitschek	De	Oliveira
3	TRUE	Rua	Frei	Henrique	De	Coimbra
3	TRUE	Avenida	Gal	Osório	De	Paiva
2	TRUE	Avenida	João	Naves	De	Ávila
2	TRUE	Rua	Pero	Vaz	De	Caminha
2	FALSE	Rua	Fernão	Dias	Paes	Leme
2	TRUE	Avenida	Eduardo	Fróes	Da	Mota
4	FALSE	Rodovia	Anel	Rodoviário	Celso	Mello	Azevedo/br-262
2	FALSE	Avenida	José	Bento	Ribeiro	Dantas
2	FALSE	Rua	Catulo	Da	Paixão	Cearense
2	FALSE	Avenida	Joaquim	Da	Costa	Lima
2	TRUE	Avenida	Bento	Munhoz	Da	Rocha	Neto
2	TRUE	Avenida	Armando	Salles	De	Oliveira
2	TRUE	Avenida	Amador	Bueno	Da	Veiga
2	FALSE	Rua	Tancredo	De	Almeida	Neves
2	FALSE	Avenida	Dorival	Cândido	Luz	De	Oliveira
2	TRUE	Rodovia	Feliciano	Salles	Da	Cunha/sp-310
3	FALSE	Avenida	Juíz	Março	Túlio	Isaac
2	TRUE	Avenida	Osvaldo	José	Do	Amaral
2	FALSE	Rodovia	Euclídes	De	Oliveira	Figueiredo/sp-563
2	TRUE	Rua	José	Lins	Do	Rego
2	TRUE	Avenida	José	Pontes	De	Almeida
2	TRUE	Rua	João	Pereira	Da	Silva
2	TRUE	Avenida	Adhemar	Pereira	De	Barros
2	TRUE	Rua	Ismael	José	Do	Nascimento
2	TRUE	Rua	Bartolomeu	Bueno	Da	Silva
3	FALSE	Rodovia	João	Da	Silva	Maia/br-135
2	TRUE	Rua	Ayrton	Senna	Da	Silva
3	FALSE	Avenida	Pastor	Martin	Luther	King	Jr
2	TRUE	Avenida	Mozart	Pinheiro	De	Lucena
2	FALSE	Avenida	Getúlio	Dorneles	Vargas	-	N
2	TRUE	Avenida	Mário	Homem	De	Melo
2	FALSE	Avenida	Daniel	De	Lã	Touche
3	FALSE	Avenida	Pastor	Martin	Luther	King	Júnior
2	TRUE	Rua	Caetano	Munhoz	Da	Rocha
2	FALSE	Rodovia	Raphael	De	Almeida	Magalhaes/br-493/rj-109

 

[pcsrvc]

Also, I have a tab that specifies whether the contents of row 1 and 2 should be ignored in order to come up with the starting row value if that helps.

 

[Sufiyan97]

I do not understand fully what you want to achieve but see and let me know what changes you need in this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Starting row	Particle Test	1	2	3	4	5	6		FIRST	MIDDLE	LAST		FIRST	MIDDLE	LAST
2	2	TRUE	Avenida	Juscelino	Kubitschek	De	Oliveira			Juscelino	Kubitschek	De Oliveira		Juscelino	Kubitschek	De Oliveira
3	2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364/br-163			Juscelino	Kubitschek	De Oliveira/br-364/br-163
4	2	FALSE	Estrada	Francisco	Da	Cruz	Nunes			Francisco	Da	Nunes
5	2	TRUE	Avenida	Ayrton	Senna	Da	Silva			Ayrton	Senna	Da Silva
6	2	TRUE	Rua	Cláudio	Manoel	Da	Costa			Cláudio	Manoel	Da Costa
7	2	FALSE	Rodovia	Ernâni	Do	Amaral	Peixoto/rj-106			Ernâni	Do	Peixoto/rj-106
8	2	TRUE	Avenida	Raimundo	Pereira	De	Magalhães			Raimundo	Pereira	De Magalhães
9	2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364			Juscelino	Kubitschek	De Oliveira/br-364
10	2	TRUE	Rua	Juscelino	Kubitschek	De	Oliveira			Juscelino	Kubitschek	De Oliveira
11	3	TRUE	Rua	Frei	Henrique	De	Coimbra			Henrique	De	De Coimbra
12	3	TRUE	Avenida	Gal	Osório	De	Paiva			Osório	De	De Paiva
13	2	TRUE	Avenida	João	Naves	De	Ávila			João	Naves	De Ávila
14	2	TRUE	Rua	Pero	Vaz	De	Caminha			Pero	Vaz	De Caminha
15	2	FALSE	Rua	Fernão	Dias	Paes	Leme			Fernão	Dias	Leme
16	2	TRUE	Avenida	Eduardo	Fróes	Da	Mota			Eduardo	Fróes	Da Mota
17	4	FALSE	Rodovia	Anel	Rodoviário	Celso	Mello	Azevedo/br-262		Celso	Mello	Mello
18	2	FALSE	Avenida	José	Bento	Ribeiro	Dantas			José	Bento	Dantas
19	2	FALSE	Rua	Catulo	Da	Paixão	Cearense			Catulo	Da	Cearense
20	2	FALSE	Avenida	Joaquim	Da	Costa	Lima			Joaquim	Da	Lima
21	2	TRUE	Avenida	Bento	Munhoz	Da	Rocha	Neto		Bento	Munhoz	Da Rocha
22	2	TRUE	Avenida	Armando	Salles	De	Oliveira			Armando	Salles	De Oliveira
23	2	TRUE	Avenida	Amador	Bueno	Da	Veiga			Amador	Bueno	Da Veiga
24	2	FALSE	Rua	Tancredo	De	Almeida	Neves			Tancredo	De	Neves
25	2	FALSE	Avenida	Dorival	Cândido	Luz	De	Oliveira		Dorival	Cândido	De
26	2	TRUE	Rodovia	Feliciano	Salles	Da	Cunha/sp-310			Feliciano	Salles	Da Cunha/sp-310
27	3	FALSE	Avenida	Juíz	Março	Túlio	Isaac			Março	Túlio	Isaac
28	2	TRUE	Avenida	Osvaldo	José	Do	Amaral			Osvaldo	José	Do Amaral
29	2	FALSE	Rodovia	Euclídes	De	Oliveira	Figueiredo/sp-563			Euclídes	De	Figueiredo/sp-563
30	2	TRUE	Rua	José	Lins	Do	Rego			José	Lins	Do Rego
31	2	TRUE	Avenida	José	Pontes	De	Almeida			José	Pontes	De Almeida
32	2	TRUE	Rua	João	Pereira	Da	Silva			João	Pereira	Da Silva
33	2	TRUE	Avenida	Adhemar	Pereira	De	Barros			Adhemar	Pereira	De Barros
34	2	TRUE	Rua	Ismael	José	Do	Nascimento			Ismael	José	Do Nascimento
35	2	TRUE	Rua	Bartolomeu	Bueno	Da	Silva			Bartolomeu	Bueno	Da Silva
36	3	FALSE	Rodovia	João	Da	Silva	Maia/br-135			Da	Silva	Maia/br-135
37	2	TRUE	Rua	Ayrton	Senna	Da	Silva			Ayrton	Senna	Da Silva
38	3	FALSE	Avenida	Pastor	Martin	Luther	King	Jr		Martin	Luther	King
39	2	TRUE	Avenida	Mozart	Pinheiro	De	Lucena			Mozart	Pinheiro	De Lucena
40	2	FALSE	Avenida	Getúlio	Dorneles	Vargas	-	N		Getúlio	Dorneles	-
41	2	TRUE	Avenida	Mário	Homem	De	Melo			Mário	Homem	De Melo
42	2	FALSE	Avenida	Daniel	De	Lã	Touche			Daniel	De	Touche
43	3	FALSE	Avenida	Pastor	Martin	Luther	King	Júnior		Martin	Luther	King
44	2	TRUE	Rua	Caetano	Munhoz	Da	Rocha			Caetano	Munhoz	Da Rocha
45	2	FALSE	Rodovia	Raphael	De	Almeida	Magalhaes/br-493/rj-109			Raphael	De	Magalhaes/br-493/rj-109
Sheet1

Cell Formulas
Range	Formula
J2:J45	J2	=INDEX($C2:$H2,,$A2)
K2:K45	K2	=INDEX($C2:$H2,,$A2+1)
L2:L45	L2	=IF(B2=TRUE,F2&" "&INDEX($C2:$H2,,5),INDEX($C2:$H2,,5))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Starting row	Particle Test	1	2	3	4	5	6	FIRST	MIDDLE	LAST
2	2	TRUE	Avenida	Juscelino	Kubitschek	De	Oliveira		Juscelino	Kubitschek	De Oliveira
3	2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364/br-163		Juscelino	Kubitschek	De Oliveira/br-364/br-163
4	2	FALSE	Estrada	Francisco	Da	Cruz	Nunes		Francisco	Da	Cruz
5	2	TRUE	Avenida	Ayrton	Senna	Da	Silva		Ayrton	Senna	Da Silva
6	2	TRUE	Rua	Cláudio	Manoel	Da	Costa		Cláudio	Manoel	Da Costa
7	2	FALSE	Rodovia	Ernâni	Do	Amaral	Peixoto/rj-106		Ernâni	Do	Amaral
8	2	TRUE	Avenida	Raimundo	Pereira	De	Magalhães		Raimundo	Pereira	De Magalhães
9	2	TRUE	Rodovia	Juscelino	Kubitschek	De	Oliveira/br-364		Juscelino	Kubitschek	De Oliveira/br-364
10	2	TRUE	Rua	Juscelino	Kubitschek	De	Oliveira		Juscelino	Kubitschek	De Oliveira
11	3	TRUE	Rua	Frei	Henrique	De	Coimbra		Henrique	De	De Coimbra
12	3	TRUE	Avenida	Gal	Osório	De	Paiva		Osório	De	De Paiva
13	2	TRUE	Avenida	João	Naves	De	Ávila		João	Naves	De Ávila
14	2	TRUE	Rua	Pero	Vaz	De	Caminha		Pero	Vaz	De Caminha
15	2	FALSE	Rua	Fernão	Dias	Paes	Leme		Fernão	Dias	Paes
16	2	TRUE	Avenida	Eduardo	Fróes	Da	Mota		Eduardo	Fróes	Da Mota
17	4	FALSE	Rodovia	Anel	Rodoviário	Celso	Mello	Azevedo/br-262	Celso	Mello	Celso
18	2	FALSE	Avenida	José	Bento	Ribeiro	Dantas		José	Bento	Ribeiro
19	2	FALSE	Rua	Catulo	Da	Paixão	Cearense		Catulo	Da	Paixão
20	2	FALSE	Avenida	Joaquim	Da	Costa	Lima		Joaquim	Da	Costa
21	2	TRUE	Avenida	Bento	Munhoz	Da	Rocha	Neto	Bento	Munhoz	Da Rocha
22	2	TRUE	Avenida	Armando	Salles	De	Oliveira		Armando	Salles	De Oliveira
23	2	TRUE	Avenida	Amador	Bueno	Da	Veiga		Amador	Bueno	Da Veiga
24	2	FALSE	Rua	Tancredo	De	Almeida	Neves		Tancredo	De	Almeida
25	2	FALSE	Avenida	Dorival	Cândido	Luz	De	Oliveira	Dorival	Cândido	Luz
26	2	TRUE	Rodovia	Feliciano	Salles	Da	Cunha/sp-310		Feliciano	Salles	Da Cunha/sp-310
27	3	FALSE	Avenida	Juíz	Março	Túlio	Isaac		Março	Túlio	Túlio
28	2	TRUE	Avenida	Osvaldo	José	Do	Amaral		Osvaldo	José	Do Amaral
29	2	FALSE	Rodovia	Euclídes	De	Oliveira	Figueiredo/sp-563		Euclídes	De	Oliveira
30	2	TRUE	Rua	José	Lins	Do	Rego		José	Lins	Do Rego
31	2	TRUE	Avenida	José	Pontes	De	Almeida		José	Pontes	De Almeida
32	2	TRUE	Rua	João	Pereira	Da	Silva		João	Pereira	Da Silva
33	2	TRUE	Avenida	Adhemar	Pereira	De	Barros		Adhemar	Pereira	De Barros
34	2	TRUE	Rua	Ismael	José	Do	Nascimento		Ismael	José	Do Nascimento
35	2	TRUE	Rua	Bartolomeu	Bueno	Da	Silva		Bartolomeu	Bueno	Da Silva
36	3	FALSE	Rodovia	João	Da	Silva	Maia/br-135		Da	Silva	Silva
37	2	TRUE	Rua	Ayrton	Senna	Da	Silva		Ayrton	Senna	Da Silva
38	3	FALSE	Avenida	Pastor	Martin	Luther	King	Jr	Martin	Luther	Luther
39	2	TRUE	Avenida	Mozart	Pinheiro	De	Lucena		Mozart	Pinheiro	De Lucena
40	2	FALSE	Avenida	Getúlio	Dorneles	Vargas	-	N	Getúlio	Dorneles	Vargas
41	2	TRUE	Avenida	Mário	Homem	De	Melo		Mário	Homem	De Melo
42	2	FALSE	Avenida	Daniel	De	Lã	Touche		Daniel	De	Lã
43	3	FALSE	Avenida	Pastor	Martin	Luther	King	Júnior	Martin	Luther	Luther
44	2	TRUE	Rua	Caetano	Munhoz	Da	Rocha		Caetano	Munhoz	Da Rocha
45	2	FALSE	Rodovia	Raphael	De	Almeida	Magalhaes/br-493/rj-109		Raphael	De	Almeida
Data

Cell Formulas
Range	Formula
I2:J45	I2	=INDEX(C2:H2,,A2+{0,1})
K2:K45	K2	=IF(B2,TEXTJOIN(" ",,INDEX(F2:G2,,0)),F2)
Dynamic array formulas.

 

[pcsrvc]

Thanks @Sufiyan97 and @Fluff you guys are the best!

 

[Fluff]

As both solutions give different results, did either of them give the result you are after?

 

[pcsrvc]

As both solutions give different results, did either of them give the result you are after?

I'm checking on both of them as we speak. I will probably be able to modify the formulas to reach what I need if the solutions the two of you provided do not match exactly what I need, thank you very much!

 

[Fluff]

Glad we could help & thanks for the feedback.