Removing Dups in same Cell

[chipsworld]

Can anyone help me with this issue?

I have a HUGE report and in most cells in the description column, there are repeated words that I would like to remove.

I have been digging through the web trying to find a way to accomplish this through either VBA or a function and have had no luck.

Here is a small example of what I am talking about... I would like to be able to remove the "MERCEDES-BENZ" repeats but leave the first one. All of the date ranges and Model info I need to keep as well.

Mercedes-Benz 400SE 1992 - 1992/Mercedes-Benz 400SEL 1993 - 1993/Mercedes-Benz 500SEC 1993 - 1993/Mercedes-Benz 500SEL 1992 - 1993/Mercedes-Benz 600SEC 1993 - 1993/Mercedes-Benz 600SEL 1992 - 1993/Mercedes-Benz CL500 1998 - 1999/Mercedes-Benz CL600 1998 - 1999/Mercedes-Benz S320 1994 - 1999/Mercedes-Benz S350D 1993 - 1994/Mercedes-Benz S420 1994 - 1999/Mercedes-Benz S500 1994 - 1999/Mercedes-Benz S600 1994 - 1999

Would do this by hand, but there are 21k+ lines of data to go through.

 

[Cubist]

Maybe this.

=LET(model,TEXTBEFORE(A1," ",1),model & SUBSTITUTE(A1,model,""))

Edit: If you want a spilled array you can try this. Grab the entire range.

=BYROW(A1:A10,LAMBDA(r,LET(model,TEXTBEFORE(r," ",1),model&SUBSTITUTE(r,model,""))))

 

[chipsworld]

Maybe this.

=LET(model,TEXTBEFORE(A1," ",1),model & SUBSTITUTE(A1,model,""))

Edit: If you want a spilled array you can try this. Grab the entire range.

=BYROW(A1:A10,LAMBDA(r,LET(model,TEXTBEFORE(r," ",1),model&SUBSTITUTE(r,model,""))))

This works (Top line)! But...(You knew there would be one...right?)

Some of the models are two words...Toyota Tacoma, Chevy Silverado, etc...

How (if possible) can I include the first two words instead if just the first one?

 

[Cubist]

This works (Top line)! But...(You knew there would be one...right?)

Some of the models are two words...Toyota Tacoma, Chevy Silverado, etc...

How (if possible) can I include the first two words instead if just the first one?

You would change the 1 to 2, but this is still manual and won't work for both cases. I'm looking for a way to accommodate both cases. Maybe someone else has more ideas.

 

[Fluff]

Maybe

=LET(a,TEXTBEFORE(TAKE(TEXTSPLIT(A2,,"/"),1,)," ",-4),a&SUBSTITUTE(A2,a,""))

 

[chipsworld]

Fluff,
No go...sorry! I do appreciate the effort, however. Can't count the number of times you have helped me in the past!

Cubist,
Played with the instance number and making it a 2 definitely helped.

New question...What if the Make Model changes (below). How could I adjust for that? OR can I?

Acura SLX 1996 - 1997/ 1998 - 1999/ Isuzu Trooper 1992 - 1997/ Isuzu Trooper 1993 - 1994/ Isuzu Trooper 1998 - 2002

Do not feel bad if not. Your one line has saved me HOURS of work! My boss was impressed as well! (Not easy to do)

 

[Cubist]

I think this should work for both cases.

=LET(s,SEQUENCE(10,1,0),m,MIN(IF(ISNUMBER(SEARCH(s,A1)),SEARCH(s,A1),"")),model,LEFT(A1,m-1), model & SUBSTITUTE(A1,model,""))

With the new question, what do you want this to look like? Remove all of the "Isuzu Trooper" or just keep the first instance?

 

[chipsworld]

I think this should work for both cases.

=LET(s,SEQUENCE(10,1,0),m,MIN(IF(ISNUMBER(SEARCH(s,A1)),SEARCH(s,A1),"")),model,LEFT(A1,m-1), model & SUBSTITUTE(A1,model,""))

With the new question, what do you want this to look like? Remove all of the "Isuzu Trooper" or just keep the first instance?

Just keep the first instance. There could actually be multiple changes in a single line if that matters.

I already feel like I owe you a meal and a beer! LOL

 

[chipsworld]

Cubist,
Thought this might help a little??

Nissan Pathfinder 1989 - 1995

Honda Passport 1994 - 1997/ Isuzu Rodeo 1991 - 1993/ Isuzu Rodeo 1994 - 1997

Mitsubishi Montero 1992 - 2000

Acura SLX 1996 - 1997/ Acura SLX 1998 - 1999/ Isuzu Trooper 1992 - 1997/ Isuzu Trooper 1993 - 1994/ Isuzu Trooper 1998 - 2002

Ford Bronco Full Size 1988 - 1991/ Ford Bronco Full Size 1992 - 1996

Chevy Blazer Full Size 1992 - 1994/ Chevy Tahoe 1995 - 1999/ GMC Yukon 1992 - 1997

A larger example to work with. This is raw data.

 

[Cubist]

I'm trying a few things but stuck. I don't know how to replace duplicates with blanks while keeping only the first value in an array like below. How to go from row 11 ->row 12.

Book2
	A	B	C	D	E
11	Acura SLX		Isuzu Trooper	Isuzu Trooper	Isuzu Trooper
12	Acura SLX		Isuzu Trooper
Sheet5

Cell Formulas
Range	Formula
A11:E11	A11	=BYCOL(A10#,LAMBDA(c,LET(s,SEQUENCE(10,1,0),m,MIN(IF(ISNUMBER(SEARCH(s,c)),SEARCH(s,c),"")),model,LEFT(c,m-1),TRIM(CLEAN(model)))))
Dynamic array formulas.