Concatenate cells Before a certain value

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
How can I concatenate a column BEFORE a cell that contains a certain value in column (D12=?????)?
Only D3:D11 should be concatenated. All cells in D3:D17 contain a Formula.
thank you

NBA.xlsm
D
2Criteria2
3Rank2, AL & AM >60%
4K, R>0, T>0
5MOV, AL:AM >= 60%
6Q, R, S, AL:AM>=60%
7Cov%2,ATS+/-,AL:AM>=60%
8ATS+/-,Mov2,AL:AM>=60%
9Cov2%,Mov2,AL:AM>=60%
10MOV2, AL:AM >= 60%
11MOV,MOV2,AL:AM >= 60%
12??????????????????
13AC, AL & AM>= 60%
14Rank2, AL >= 60%
15Q>=60%, S>=6
16MOV2 > ATS
17Cover2% > 60%
Indicators
Cell Formulas
RangeFormula
D3:D17D3=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
as you have TEXTJOIN
what delimeter do you want between the items

wil the ??? always be the exact number

you have not pasted column A, so i just get a vlaue error in the xl2bb
so not100 % what you are doing exactly

and nit sure what the expected results are

=TEXTJOIN("/",,INDIRECT("d2:d"&MATCH("?????????",D2:D1000000)))

Book6
ABCDEF
1Criteria2
2A1A1/A2/A3/A4/A5/A6
3A2
4A3
5A4
6A5
7A6
8?????????
9A8
10A9
11A10
12A11
13A12
14A13
15A14
16A15
Sheet1
Cell Formulas
RangeFormula
F2F2=TEXTJOIN("/",,INDIRECT("d2:d"&MATCH("?????????",D2:D1000000)))


but the match will return the row() number based on where the range ot looks in starts - so need to be careful of that
 
Upvote 0
etaf said:
as you have TEXTJOIN
what delimeter do you want between the items

wil the ??? always be the exact number

you have not pasted column A, so i just get a vlaue error in the xl2bb
so not100 % what you are doing exactly

and nit sure what the expected results are

=TEXTJOIN("/",,INDIRECT("d2:d"&MATCH("?????????",D2:D1000000)))

Book6
ABCDEF
1Criteria2
2A1A1/A2/A3/A4/A5/A6
3A2
4A3
5A4
6A5
7A6
8?????????
9A8
10A9
11A10
12A11
13A12
14A13
15A14
16A15
Sheet1
Cell Formulas
RangeFormula
F2F2=TEXTJOIN("/",,INDIRECT("d2:d"&MATCH("?????????",D2:D1000000)))


but the match will return the row() number based on where the range ot looks in starts - so need to be careful of that
Click to expand...
your formula is concatenating the correct cells, but it needs to be the cell location (sorry, I did not mention in my original post).
ie. =CONCATENATE(A3,A4,A5,A6,A7,A8,A9,A10,A11) should be the answer.
Yes, I will use "????" will be the exact number.

NBA.xlsm
ABCD
1Row 4Row 5
2As FAV As FAVRankCriteria2
3IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL & AM >60%"IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL & AM >60%"1Rank2, AL & AM >60%
4,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($T4),($T4>0)),"K, R>0, T>0",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($T5),($T5>0)),"K, R>0, T>0"2K, R>0, T>0
5,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV, AL:AM >= 60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV, AL:AM >= 60%"2MOV, AL:AM >= 60%
6,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%"2Q, R, S, AL:AM>=60%
7,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Cov%2,ATS+/-,AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Cov%2,ATS+/-,AL:AM>=60%"2Cov%2,ATS+/-,AL:AM>=60%
8,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($S4),($S4>0),$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"ATS+/-,Mov2,AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($S5),($S5>0),$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"ATS+/-,Mov2,AL:AM>=60%"2ATS+/-,Mov2,AL:AM>=60%
9,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$S4>$S5,($S4>0),$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Cov2%,Mov2,AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Cov2%,Mov2,AL:AM>=60%"2Cov2%,Mov2,AL:AM>=60%
10,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV2, AL:AM >= 60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV2, AL:AM >= 60%"8MOV2, AL:AM >= 60%
11,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV,MOV2,AL:AM >= 60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV,MOV2,AL:AM >= 60%"9MOV,MOV2,AL:AM >= 60%
12,IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))))))))))))))))))))),IF(AND($A4=$AQ4,$B4<0,$B4=100),"????","")))))))))))))))))))))))))))#VALUE!????
13,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$AC4>=$AC5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"AC, AL & AM>= 60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$AC4<=$AC5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"AC, AL & AM>= 60%"11AC, AL & AM>= 60%
14,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6)),"Rank2, AL >= 60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6)),"Rank2, AL >= 60%"13Rank2, AL >= 60%
15,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>=0.6),ISNUMBER($S4),($S4>=6)),"Q>=60%, S>=6",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>=0.6),ISNUMBER($S5),($S5>=6)),"Q>=60%, S>=6"12Q>=60%, S>=6
16,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($T4),($T4>0)),"MOV2 > ATS",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($T5),($T5>0)),"MOV2 > ATS"15MOV2 > ATS
Indicators
Cell Formulas
RangeFormula
C3:C16C3=RANK(E3,Rank)
D3:D16D3=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
 
Upvote 0
not sure about the xl2bb

so you want to actually concatenate the values in column A - not D as i have shown
starting at row 3
down to the row before it finds ????????

anyway
=TEXTJOIN("/",,INDIRECT("A3:A"&MATCH("?????????",D3:D1000001)+1))
note the +1

Book9
ABCDEF
1
2
3columnA3columnA3/columnA4/columnA5/columnA6/columnA7/columnA8/columnA9/columnA10
4columnA4
5columnA5
6columnA69
7columnA7
8columnA8
9columnA9
10columnA10
11columnA11?????????
12columnA12
13columnA13
14columnA14
15columnA15
16columnA16
17columnA17
18columnA18
19columnA19
20columnA20
21columnA21
22columnA22
23columnA23
24columnA24
25columnA25
26columnA26
27
Sheet1
Cell Formulas
RangeFormula
F3F3=TEXTJOIN("/",,INDIRECT("A3:A"&MATCH("?????????",D3:D1000001)+1))
F6F6=MATCH("?????????",D3:D1000001)
 
Upvote 0
etaf said:
not sure about the xl2bb

so you want to actually concatenate the values in column A - not D as i have shown
starting at row 3
down to the row before it finds ????????

anyway
=TEXTJOIN("/",,INDIRECT("A3:A"&MATCH("?????????",D3:D1000001)+1))
note the +1

Book9
ABCDEF
1
2
3columnA3columnA3/columnA4/columnA5/columnA6/columnA7/columnA8/columnA9/columnA10
4columnA4
5columnA5
6columnA69
7columnA7
8columnA8
9columnA9
10columnA10
11columnA11?????????
12columnA12
13columnA13
14columnA14
15columnA15
16columnA16
17columnA17
18columnA18
19columnA19
20columnA20
21columnA21
22columnA22
23columnA23
24columnA24
25columnA25
26columnA26
27
Sheet1
Cell Formulas
RangeFormula
F3F3=TEXTJOIN("/",,INDIRECT("A3:A"&MATCH("?????????",D3:D1000001)+1))
F6F6=MATCH("?????????",D3:D1000001)
Click to expand...
I'm trying your formula but it's giving me an error?
NBA.xlsm
IJK
1HeaderHeader2
2A3B3Concatenate A
3A4B4#N/A
4A5B5Concatenate B
5A6B6#N/A
6A7B7
7A8B8
8A9B9
9A10B10
10A11B11
11????????
12A13B13
13A14B14
14A15B15
15A16B16
16A17B17
17A18B18
18A19B19
19A20B20
20A21B21
21A22B22
22A23B23
23A24B24
24A25B25
25A26B26
26A27B27
Indicators
Cell Formulas
RangeFormula
K3K3=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I1000)))
K5K5=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000)))
 
Upvote 0
sorry, i left the exact part of
=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I26,0)))
=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))

Book10
IJK
1HeaderHeader2
2A3B3Concatenate A
3A4B4A3,A4,A5,A6,A7,A8,A9,A10,A11
4A5B5Concatenate B
5A6B6B3,B4,B5,B6,B7,B8,B9,B10,B11
6A7B7
7A8B8
8A9B9
9A10B10
10A11B11
11????????
12A13B13
13A14B14
14A15B15
15A16B16
16A17B17
17A18B18
18A19B19
19A20B20
20A21B21
21A22B22
22A23B23
23A24B24
24A25B25
25A26B26
26A27B27
Sheet1
Cell Formulas
RangeFormula
K3K3=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I26,0)))
K5K5=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))
 
Upvote 0
etaf said:
sorry, i left the exact part of
=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I26,0)))
=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))

Book10
IJK
1HeaderHeader2
2A3B3Concatenate A
3A4B4A3,A4,A5,A6,A7,A8,A9,A10,A11
4A5B5Concatenate B
5A6B6B3,B4,B5,B6,B7,B8,B9,B10,B11
6A7B7
7A8B8
8A9B9
9A10B10
10A11B11
11????????
12A13B13
13A14B14
14A15B15
15A16B16
16A17B17
17A18B18
18A19B19
19A20B20
20A21B21
21A22B22
22A23B23
23A24B24
24A25B25
25A26B26
26A27B27
Sheet1
Cell Formulas
RangeFormula
K3K3=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I26,0)))
K5K5=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))
Click to expand...
thank you.
how can I create a formula using those results? (K15 & K17)
NBA.xlsm
IJK
1HeaderHeader2
2A3B3Concatenate A
3A4B4A3,A4,A5,A6,A7,A8,A9,A10,A11
4A5B5Concatenate B
5A6B6B3,B4,B5,B6,B7,B8,B9,B10,B11
6A7B7
7A8B8concatenate(A3,A4,A5,A6,A7,A8,A9,A10,A11)
8A9B9concatenate(B3,B4,B5,B6,B7,B8,B9,B10,B11)
9A10B10
10A11B11
11????????=concatenate(A3,A4,A5,A6,A7,A8,A9,A10,A11)
12A13B13=concatenate(B3,B4,B5,B6,B7,B8,B9,B10,B11)
13A14B14
14A15B15
15A16B16new formula 1
16A17B17
17A18B18new formula 2
18A19B19
19A20B20
20A21B21
21A22B22
22A23B23
23A24B24
24A25B25
25A26B26
26A27B27
Indicators
Cell Formulas
RangeFormula
K3K3=TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I1000,0)))
K5K5=TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))
K7K7="concatenate("&TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I1000,0)))&")"
K8K8="concatenate("&TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1000,0)))&")"
K11K11="=concatenate("&TEXTJOIN(",",,INDIRECT("i2:i"&MATCH("????",I2:I1004,0)))&")"
K12K12="=concatenate("&TEXTJOIN(",",,INDIRECT("j2:j"&MATCH("????",J2:J1004,0)))&")"
 
Upvote 0
what formula and what do they represent ,

not following ?
 
Upvote 0
etaf said:
what formula and what do they represent ,

not following ?
Click to expand...
I wanted to include your formula in the Concatenate formula.
CONCATENATE(TEXTJOIN(",",,INDIRECT("A3:A"&MATCH("????",D3:D1005,0)+1)))
This way I don't have to re-adjust the concat formula every time I sort the columns.

And it works.
Thx for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
139
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
133
gtd526
gtd526
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
131
gtd526
gtd526
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
170
Tetra201
Tetra201
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
162
gtd526
gtd526

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top