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
70
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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.
 

rline101

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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)
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70

ADVERTISEMENT

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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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)
 
Solution

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70

ADVERTISEMENT

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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.
 

rline101

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,147,634
Messages
5,742,240
Members
423,717
Latest member
rubthenut

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
Top