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

rline101

Board Regular
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.

rline101

Board Regular
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)

Main Sheet

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

'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.

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

DanteAmor

Well-known Member
Again with pleasure. Thanks for the feedback

Replies
0
Views
391
Replies
2
Views
321
Replies
18
Views
15K
Replies
3
Views
276
Replies
1
Views
1K

1,147,564
Messages
5,741,856
Members
423,691
Latest member

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.

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

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