Replace duplicate words in cell from pool of alternate words so that no word in pool appears more than once

[rline101]

Hi,
I have a generated report comment in a cell. This may possibly contain multiple instances of each of the words "outstanding","exemplary","exceptional","superb". (These words are placed there using choose(randbetween(1,4),etc) so it's also quite possible for there to be no duplicates.) There could be 3 superbs and 1 exceptional, or 2 exemplarys and 2 superbs, or 4 exemplarys, etc etc etc.

I am trying to find a way to determine any duplicates, and replace with another word from that same set. But the tricky part (to me) is that let's say I have outstanding, outstanding, superb, exceptional to begin with, and I replace the 2nd outstanding with "superb", now I have duplicates of superb.

So I need something which will look in that cell and find any instances of duplicates of any words from that set, then replace the 2nd, 3rd or 4th occurrence of that word with another (or other) words from that set so that the maximum number of times each word in that set appears is 1.

For context, the sentences, in the order they appear, are...

John displayed {outstanding} work habits...
John's {outstanding} semester examination reveals an {outstanding} knowledge base across all topics studied...
John has had an {outstanding} year in ...

These appear in different cells, but the final result appears in one cell (it concatenates everything before it). Let's say the final (simplified) concatenated result is "John displayed {outstanding} work habits in class. John's {outstanding} semester examination reveals an {outstanding} knowledge base across all topics studied. John has had an {outstanding} year in Mathematics."

I have worked out how to do a simple replacement of one with another, what I can't work out is how to make it work with choosing from a pool of words, and excluding words that already exist in the cell.

I am happy to try and explain further if needed. For simplicity, assume the cell needing to be dealt with is A1, and the pool of words sits in C1:C4, and I would like the result in B1.

I would prefer a non-VBA solution but if that's the only way to get it done, VBA is fine.

Many thanks for any advice.

 

[rline101]

OK, I managed to do it with one auxiliary column. There is no way I could have got this done without your help Dante, so thankyou heaps

For the record, here is what I did (column refs are the actual col refs in my spreadsheet)

Sheet Grades P1:U4

Main Sheet

Column BE: =IF($A2="","",RANDBETWEEN(1,4))

Column BF: =IF($A2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BD2,Grades!$P$1,"@@"),Grades!$P$2,"@@"),Grades!$P$3,"@@"),Grades!$P$4,"@@"),"@@",INDEX(Grades!$P$1:$P$4,MATCH(MOD(MOD(BE2,4)+1,4)+1,Grades!$R$1:$R$4,0)),1),"@@",INDEX(Grades!$P$1:$P$4,MATCH(MOD(MOD(BE2,4)+2,4)+1,Grades!$R$1:$R$4,0)),1),"@@",INDEX(Grades!$P$1:$P$4,MATCH(MOD(MOD(BE2,4)+3,4)+1,Grades!$R$1:$R$4,0)),1),"@@",INDEX(Grades!$P$1:$P$4,MATCH(MOD(MOD(BE2,4)+4,4)+1,Grades!$R$1:$R$4,0)),1))

'm not sure if there's some simpler way of dealing with the mod(mod( part, but it's not too bad considering it works. The formula above takes care of repeats of "outstanding..." etc and of "gained..." etc.

 

[DanteAmor]

Again with pleasure. Thanks for the feedback