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.

 

[Radoslaw Poprawski]

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]

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]

How about:

Dante Amor
	A	B	C
1	John 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
2	John 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
3			exceptional
4			superb
Hoja12

Cell Formulas
Range	Formula
A2	A2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, C1,"@@"),C2,"@@"),C3,"@@"),C4,"@@"), "@@",C1,1),"@@",C2,1),"@@",C3,1),"@@",C4,1)

 

[rline101]

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]

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
	A	B	C	D	E
1	John 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	0.27140362	4
2	John 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		exemplary	0.73753115	1
3			exceptional	0.60906758	2
4			superb	0.28556618	3
Hoja12

Cell Formulas
Range	Formula
D1:D4	D1	=RAND()
E1:E4	E1	=RANK(D1,$D$1:$D$4)
A2	A2	=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)

 

[rline101]

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]

And I don't think there'll be more surprises

That never happens .
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]

That never happens .
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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1					2		3		4		5		6		7		8		9		10		11		12		13		14		15		16		17		18		19		20
2	John 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	outstanding	0.61	3	0.05	4	0.62	2	0.44	4	0.99	1	0.62	3	0.82	1	0.66	2	0.04	4	0.73	1	0.59	1	0.18	1	0.4	3	0.8	1	0.69	3	0.04	4	0.15	3	0.3	4	0.08	4
3	David 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	exemplary	0.79	2	0.51	2	0.53	3	0.52	2	0.55	3	0.69	2	0.59	2	0.4	4	0.35	2	0.61	2	0.07	3	0	4	0.13	4	0.19	4	0.85	2	0.14	3	0.62	2	0.81	2	0.15	3
4	Nancy 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	exceptional	0.45	4	0.29	3	0.41	4	0.52	3	0.9	2	0.82	1	0.29	3	0.55	3	0.31	3	0.04	4	0.51	2	0.09	3	0.44	2	0.4	2	0.65	4	0.88	1	0.62	1	0.55	3	0.15	2
5	Pat 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	superb	0.93	1	0.68	1	0.78	1	0.96	1	0.36	4	0.24	4	0.1	4	0.75	1	0.4	1	0.54	3	0.07	4	0.18	2	0.46	1	0.2	3	1	1	0.72	2	0.01	4	0.86	1	0.91	1
6	Nic 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
Range	Formula
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:F5	D2	=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:G5	E2	=RANK(D2,D$2:D$5)
B2:B6	B2	=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)