Unique Keyword List Error

[maddog9486]

I have a data set of 954 questions that i am attempting to generate a unique list of keywords from. I am using the following code to generate a partial list, but I cannot extend past row 124 (i get a #CALC! error if i change 124 to any higher value). I suspect this is due to the character limit of TEXTJOIN. Is there a better way to do this to overcome the limit? I would prefer to avoid macros if possible, but if that is the only option, that will be ok.

=UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FAQ!C5:C124,", ","</s><s>"),"?","</s><s>"),CHAR(146),"</s><s>"),":","</s><s>"),".","</s><s>"),"""","</s><s>"),"(","</s><s>"),")","</s><s>"),"'","</s><s>"),CHAR(10),"</s><s>")," ","</s><s>"),"</s><s></s><s></s><s>","</s><s>"),"</s><s></s><s>","</s><s>"))&"</s></t>","//s"))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[maddog9486]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the suggestion. It's been a while since i've been on the forum. I was not aware that was an option. I've updated it now.

 

[Fluff]

Thanks for that.
Can you get all the rows using, that way they can be split later.

=TEXTJOIN(",",,FAQ!C5:C124)

 

[maddog9486]

No, the furthest row I can get to with just a straight TEXTJOIN is 216 (my current questions set goes to 954, but could expand). I did check the number of characters within all the cells and it totals 140,389, hence where i believe i am facing the TEXTJOIN issue.

As soon as I figure out how to attach a file, I can share the sample workbook.

 

[Fluff]

You cannot attach files here, you would need to upload it to a share site such as OneDrive, GoogleDrive, DropBox. Then mark for sharing & post the link you are given.

 

[maddog9486]

Here is a sample file. The Questions in Column A and my attempt at a unique keyword list in column D. I can only select the range A2:A121 in the keyword formula. Anything above that nets a the #CALC! error.

Sample.xlsx

 

[Fluff]

Thanks for that. How about

=UNIQUE(DROP(REDUCE("",A2:A951,LAMBDA(x,y,VSTACK(x,TOCOL(TEXTSPLIT(y,CHAR(10),{","," ","?","'",":","(",")",".","""","’"},1),3)))),1))

although it might be a bit slow depending on your computer.

 

[maddog9486]

Thanks for that. How about

=UNIQUE(DROP(REDUCE("",A2:A951,LAMBDA(x,y,VSTACK(x,TOCOL(TEXTSPLIT(y,CHAR(10),{","," ","?","'",":","(",")",".","""","’"},1),3)))),1))

although it might be a bit slow depending on your computer.

You sir are a genius. This appears to work perfectly. Only took 2-3 seconds to run for me. This is above my knowledge base so I need to do some homework to understand the inner workings of this. Thank you @Fluff.

 

[Fluff]

Glad to help & thanks for the feedback.