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

rline101

Board Regular
Joined
Dec 22, 2005
Messages
71
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
instead of replacing it after the fact, why don't you use =unique function to disable the possibility for duplicates?
Option 2:
use Find to determine the starting position of ur keyword
substitute all repeated keyword with ""
use the starting position-1 from find to insert ur text.
 
Upvote 0
instead of replacing it after the fact, why don't you use =unique function to disable the possibility for duplicates?
Option 2:
use Find to determine the starting position of ur keyword
substitute all repeated keyword with ""
use the starting position-1 from find to insert ur text.
Option 1, unique, does not appear in my functions list. I'm not really sure what you're meaning by option 2.
 
Upvote 0
How about:

Dante Amor
ABC
1John displayed {exceptional} work habits... John's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... John has had an {outstanding} year in outstanding
2John displayed {outstanding} work habits... John's {exemplary} semester examination reveals an {exceptional} knowledge base across all topics studied... John has had an {superb} year in exemplary
3exceptional
4superb
Hoja12
Cell Formulas
RangeFormula
A2A2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, C1,"@@"),C2,"@@"),C3,"@@"),C4,"@@"), "@@",C1,1),"@@",C2,1),"@@",C3,1),"@@",C4,1)
 
Upvote 0
Wow Dante, that is such a nice approach! Thankyou. I've got it working mostly. Now, the thing that would make this perfect is if there's a way to randomise which of the pool words appears 1st, 2nd, 3rd. Currently it always puts the words in the order of my list C1:C4. Normally I would use choose(randbetween to randomise, but in this case, I'm not sure how it's possible. Do you think there's a way to modify your code to achieve that?
 
Upvote 0
if there's a way to randomise
We need to add 2 auxiliary columns.
Note: But since they are formulas, every time you update the sheet, the words in the text will also be updated.

Dante Amor
ABCDE
1John displayed {exceptional} work habits... John's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... John has had an {outstanding} year in outstanding0.271403624
2John displayed {exemplary} work habits... John's {exceptional} semester examination reveals an {superb} knowledge base across all topics studied... John has had an {outstanding} year in exemplary0.737531151
3exceptional0.609067582
4superb0.285566183
Hoja12
Cell Formulas
RangeFormula
D1:D4D1=RAND()
E1:E4E1=RANK(D1,$D$1:$D$4)
A2A2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, C1,"@@"),C2,"@@"),C3,"@@"),C4,"@@"), "@@",INDEX(C1:C4,MATCH(1,E1:E4,0)),1),"@@",INDEX(C1:C4,MATCH(2,E1:E4,0)),1),"@@",INDEX(C1:C4,MATCH(3,E1:E4,0)),1),"@@",INDEX(C1:C4,MATCH(4,E1:E4,0)),1)
 
Upvote 0
Solution
OK, again, thankyou! I'm really enjoying seeing what you're doing and it's helping me to learn better. I think what I haven't made clear previously is that there will be, maybe 20 of these comments, for different students. So at present, the revised solution does what I asked, but it does it in the same order for every student. So, if it produces "...exemplary...exceptional...superb...outstanding..." for student 1, it will produce the same order for students 2-20. What I was hoping for was a way to randomise the order for each individual student. So student 1 gets "...exemplary...exceptional...superb...outstanding..." but student 2 gets, say, "superb...exemplary...outstanding...exceptional". This is my fault for not being clearer about this.

What I've done so far is to use the row() function for the match, so instead of match(1...), match(2...) etc it's now MATCH(MOD(ROW(A2),4)+1...) MATCH(MOD(ROW(A3),4)+1...) This seems to be cycling through the options, so every 4th row will produce the same order. That's a good interim result. But I'm wondering if there's a way to randomise the order among students, as I've said above.

You've been a big help already so thankyou. If there's a nice way to do this I'd love to know. (And I don't think there'll be more surprises...)
 
Upvote 0
And I don't think there'll be more surprises
That never happens :LOL: .
I ran out of ideas. We hope if someone else helps us with the random from the random.
My next solution would be with VBA.
 
Upvote 0
That never happens :LOL: .
I ran out of ideas. We hope if someone else helps us with the random from the random.
My next solution would be with VBA.
Ah, no problems. I massively appreciate you sticking with it and your help. Thankyou very much. I will keep thinking about it. It's so close to being done I'd like to think VBA can be avoided...
 
Upvote 0
It's so close to being done


With this option we need 40 auxiliary columns ?
Check if it works for you. Maybe it will help you to come up with a new idea.

Dante Amor
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1234567891011121314151617181920
2John displayed {exceptional} work habits... John's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... John has had an {outstanding} year in John displayed {superb} work habits... John's {exemplary} semester examination reveals an {outstanding} knowledge base across all topics studied... John has had an {exceptional} year in outstanding0.6130.0540.6220.4440.9910.6230.8210.6620.0440.7310.5910.1810.430.810.6930.0440.1530.340.084
3David displayed {exceptional} work habits... David's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... David has had an {outstanding} year in David displayed {superb} work habits... David's {exemplary} semester examination reveals an {exceptional} knowledge base across all topics studied... David has had an {outstanding} year in exemplary0.7920.5120.5330.5220.5530.6920.5920.440.3520.6120.073040.1340.1940.8520.1430.6220.8120.153
4Nancy displayed {exceptional} work habits... Nancy's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... Nancy has had an {outstanding} year in Nancy displayed {superb} work habits... Nancy's {outstanding} semester examination reveals an {exemplary} knowledge base across all topics studied... Nancy has had an {exceptional} year in exceptional0.4540.2930.4140.5230.920.8210.2930.5530.3130.0440.5120.0930.4420.420.6540.8810.6210.5530.152
5Pat displayed {exceptional} work habits... Pat's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... Pat has had an {outstanding} year in Pat displayed {superb} work habits... Pat's {exemplary} semester examination reveals an {exceptional} knowledge base across all topics studied... Pat has had an {outstanding} year in superb0.9310.6810.7810.9610.3640.2440.140.7510.410.5430.0740.1820.4610.23110.7220.0140.8610.911
6Nic displayed {exceptional} work habits... Nic's {outstanding} semester examination reveals an {exceptional} knowledge base across all topics studied... Nic has had an {outstanding} year in Nic displayed {outstanding} work habits... Nic's {exceptional} semester examination reveals an {exemplary} knowledge base across all topics studied... Nic has had an {superb} year in
Hoja12
Cell Formulas
RangeFormula
D2:D5,AN2:AN5,AL2:AL5,AJ2:AJ5,AH2:AH5,AF2:AF5,AD2:AD5,AB2:AB5,Z2:Z5,X2:X5,V2:V5,T2:T5,R2:R5,P2:P5,N2:N5,L2:L5,J2:J5,H2:H5,F2:F5D2=RAND()
E2:E5,AO2:AO5,AM2:AM5,AK2:AK5,AI2:AI5,AG2:AG5,AE2:AE5,AC2:AC5,AA2:AA5,Y2:Y5,W2:W5,U2:U5,S2:S5,Q2:Q5,O2:O5,M2:M5,K2:K5,I2:I5,G2:G5E2=RANK(D2,D$2:D$5)
B2:B6B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $C$2,"@@"),$C$3,"@@"),$C$4,"@@"),$C$5,"@@"),"@@", INDEX($C$2:$C$5,SUMPRODUCT(($E$1:$Y$1=ROW())*($E$2:$Y$5=1)*(ROW($C$2:$C$5)))-1),1),"@@", INDEX($C$2:$C$5,SUMPRODUCT(($E$1:$Y$1=ROW())*($E$2:$Y$5=2)*(ROW($C$2:$C$5)))-1),1),"@@", INDEX($C$2:$C$5,SUMPRODUCT(($E$1:$Y$1=ROW())*($E$2:$Y$5=3)*(ROW($C$2:$C$5)))-1),1),"@@", INDEX($C$2:$C$5,SUMPRODUCT(($E$1:$Y$1=ROW())*($E$2:$Y$5=4)*(ROW($C$2:$C$5)))-1),1)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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