3 variable find and replace

N

ncspndoc

I am working on a spreadsheet where I have a need to find 3 different items
and replace them with the first item as follows:

Smith
A
B
C
Jones
A
C
Green
B
C

It needs to come out like this:

Smith
Smith
Smith
Smith
Jones
Jones
Jones
Green
Green
Green

Any thoughts?

Thanks in advance for all insights and help.
 
G

Gary''s Student

Let's say your data is in column A. In B1 enter =A1
In B2 enter =IF(LEN(A2)=1,B1,A2) and copy down
 
N

ncspndoc

That is close and I think I see the issue. In the example I posted the names
and variables are the same length which was accidental. The sheet I'm
working on the variables A, B, and C in the example are varying lengths but
repeat in various combinations of the three. IE Brown, Red, Orange.

When the search sees brown, red, or orange it needs to replace it with
smith. Then when it doesn't see brown, red, orange, it needs to note that
and then replace the following series containing brown, red, or orange with
the item that isn't like the rest.

So this:

Smith
Brown
Red
Orange
Jones
Orange
Williams
Brown
Orange

Needs to become this:

Smith
Smith
Smith
Smith
Jones
Jones
Williams
Williams
Williams

Thanks, I really appreciate the help.
 
R

Ragdyer

Just replace the
"LEN(A2)=1"
in Gary's formula with:
"OR(A2={"Brown","Red","Orange"})"

Which makes the formula:

=IF(OR(A2={"Brown","Red","Orange"}),B1,A2)
 
Top