Help! Can I add the index or offset function to my textjoin formula? I need to skip every other column

[Linzexcel]

I have a workbook that unfortunately has merged cells. I am using the textjoin function to get multiple lines of data into one cell (on another sheet) but i need to drag this formula down but had it skip every other column in order to skip empty/merged ones. I have tried adding the index function and the offset function to the textjoin formula i have set up but it is NOT working. Is this even possible to do? Can you add the ability to skip columns or rows to any formula? Thank you!

 

[KRice]

Here is one option:

Book1
	A	B	C	D	E	F	G	H	I	J
1	a	b	c	d	e	f	g		skip rows	a-c-e-g-i-k-m-o
2	b								skip cols	a-c-e-g
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j
11	k
12	l
13	m
14	n
15	o
16	p
Sheet3

Cell Formulas
Range	Formula
J1	J1	=TEXTJOIN("-",TRUE,IF(MOD(SEQUENCE(ROWS(A1:A16)),2)=1,A1:A16,""))
J2	J2	=TEXTJOIN("-",TRUE,IF(MOD(SEQUENCE(,COLUMNS(A1:G1)),2)=1,A1:G1,""))

Edited to show rows and columns options

 

[Linzexcel]

Thank you for the quick reply! I tried adding this to my textjoin formula but I am just getting an error saying there is a problem with the formula...it's probably something i did wrong. Where you have A1:G1, i replaced that with the columns from the other sheet that it needs to come from...other than that i left it as it...what am i doing wrong?

 

[KRice]

I don't have enough information for a good answer...could you copy and paste the formula you are using?...and what is the error message?

 

[Linzexcel]

=TEXTJOIN("; ",TRUE,R$27:R$61,R$69:R$85),(MOD(SEQUENCE(,COLUMNS(O12:Z12)),2)=1),O12:Z12,""))

So, basically I want to be able to drag that textjoin formula down but have it skip a column...so the next line after this would be T$27:T:61,T$69:T$85 and so on and so forth. The error I am getting is it simply says "There is an error with this formula. Not trying to type a formula? etc" (And just to be clear, the textjoin part of the formula is working fine, it's just figuring out how to skip that darn column when I drag down). Thank you for your help!

 

[KRice]

It is not clear to me what you are trying to do. My formula used an IF construction to determine whether the column number was odd or even...and then based on that, it either uses the cell contents or replaces it with a blank. Your latest formula does not do that...there is an unnecessary right parentheses following R$85, and there is no IF component.

Are you interested in concatenating three different regions, or two? I can't tell from your formula. I see a vertical range R$27:R$61 and a second vertical range R$69:R$85. Then I see some semblance of my column-skipping formula that mentions the horizontal range O12:Z12. Are you trying to concatenate only the first two vertical ranges and then jump to the next two sets T$27:T:$61 and T$69:T$85 when the formula is pulled down a row? And O12:Z12 is not involved at all?

 

[KRice]

I'm guessing that you want only two ranges. Have a look at this and see if it does what you want. This takes the 1st, 3rd, 5th,... columns as the formula is pulled down. A minor adjustment would return the 2nd, 4th, 6th....columns, depending on what is needed:

MrExcel_20220908.xlsx
	R	S	T	U	V	W	X	Y	Z	AA
26										Results
27	a	a	x	a	6	a	p	a	p	a; b; c; g; z; 1; 2; 3; 7; 8; 9
28	b	b	y	b	7	b	q	b	d	x; y; c; g; z; 1; 2; 3; 7; 8; 9
29	c	c	c	c	c	c	8	c	q	6; 7; c; g; z; 1; 2; 3; 7; 8; 9
30									x	p; q; 8; g; z; 1; 2; 3; 7; 8; 9
31	g	g	g	g	g	g	g	g	y	p; d; q; x; y; z; a; b; c; 99; 88; 2; 3; 7; 8; 9
32									z
33									a
34									b
35									c
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61	z	z	z	z	z	z	z	z	99
62
68
69	1	1	1	1	1	1	1	1	88
70	2	2	2	2	2	2	2	2	2
71	3	3	3	3	3	3	3	3	3
72
73
74
75
76
77
78
79
80
81
82
83	7	7	7	7	7	7	7	7	7
84	8	8	8	8	8	8	8	8	8
85	9	9	9	9	9	9	9	9	9
Sheet3

Cell Formulas
Range	Formula
AA27:AA31	AA27	=TEXTJOIN("; ",TRUE,INDEX(R$27:Z$61,,ROWS(AA$27:AA27)*2-1),INDEX(R$69:Z$85,,ROWS(AA$27:AA27)*2-1))

 

[Linzexcel]

Yes this is essentially what I am wanting to do! Let me give it a go...i may need additional help as this is a new function for me but i really appreciate your help thus far!

 

[Linzexcel]

OK! I think we are getting somewhere but not quite there. I used your formula but changed ROWS to COLUMNS and that grabbed the info from all of the cells that I needed. But I am still having my original issue...when I drag it down it does not skip a column. When I drag it down, it is grabbing the same info from that first formula, not the ones in the 2nd column over. So dragging it down, the output is all of the same, from column R

 

[Linzexcel]

*When i use ROWS, it gives me a REF error...when I use COLUMNS, it indeed pulls the info I need, it just doesn't get the other columns info when I drag the formula down.