A Use a formula as a delimiter for TEXTJOIN

[wdgor]

I am using the TEXTJOIN formula below to generate the text presented.

=TEXTJOIN(CHAR(10)&CHAR(42)&" ",TRUE,IF(('Test 2022'!$W:$W=$A4)*('Test 2022'!$AB:$AB="Jan")*('Test 2022'!$D:$D="Yes"),'Test 2022'!$F:$F,""))

Text:

legacy pricing, fees start when training and implementation starts on 3/1/2022 * ramp up pricing over first 2 years, yearly fees due to ARR under $5k * Requires additional contract, 2 fee structures for order form and disclosure

It works perfectly in that it creates an "*" and line feed between each element. I would like to replace the "*" with a sequential number so the output would be numbered:

1. legacy pricing, fees start when training and implementation starts on 3/1/2022
2. ramp up pricing over first 2 years, yearly fees due to ARR under $5k
3. Requires additional contract, 2 fee structures for order form and disclosure

Is that possible with TEXTJOIN? Thanks for your review and suggestions.

 

[jasonb75]

See if this does what you need. I've tested it on a simple set up and it looks like it is working correctly but I haven't tested it with the full range of criteria.

=IFERROR(LET(f,FILTER('Test 2022'!$F:$F,('Test 2022'!$W:$W=$A4)*('Test 2022'!$AB:$AB="Jan")*('Test 2022'!$D:$D="Yes")),c,CONCAT(SEQUENCE(ROWS(f))&". "&f&CHAR(10)),LEFT(c,LEN(c)-1)),"")

 

[wdgor]

It returned a completely blank cell.

 

[jasonb75]

Maybe you don't have all of the functions it needs. Which version of excel are you using?

 

[wdgor]

Office 365

 

[jasonb75]

It should work then, as long as you're up to date.

See what this part does on its own, if it shows #NAME? then it means that your version of 365 is not up to date and is missing some of the functions needed. If it shows #CALC! then it means that you have the functions and that I need to do some more testing with the formula to see why it is not working correctly.

=FILTER('Test 2022'!$F:$F,('Test 2022'!$W:$W=$A4)*('Test 2022'!$AB:$AB="Jan")*('Test 2022'!$D:$D="Yes"))

 

[wdgor]

It returned #CALC!

 

[jasonb75]

I've just set up a test for the full formula while I was waiting for you to check that and it is working fine for me. The only other thing that comes to mind is that your data has changed and no longer meets the criteria.

Does this return 3?

=SUMPRODUCT(('Test 2022'!$W:$W=$A4)*('Test 2022'!$AB:$AB="Jan")*('Test 2022'!$D:$D="Yes"))

 

[wdgor]

It returns 0. BTW, I am current with Office 365. Thanks for working on this.

 

[jasonb75]

It seems like there is a problem with the data, could you check it with your original formula and see if that still gives a result?

It's gone midnight here so I'm done for today but will pick this up again in the morning.