3 variable find and replace
 

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.

3 variable find and replace
 

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
--
Gary's Student


"ncspndoc" wrote:

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.

3 variable find and replace
 

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.

"Gary''s Student" wrote:

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
--
Gary's Student


"ncspndoc" wrote:

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.

3 variable find and replace
 

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ncspndoc" wrote in message
...
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.

"Gary''s Student" wrote:

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
--
Gary's Student


"ncspndoc" wrote:

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.

3 variable find and replace
 

Ragdyer -

That is exactly what I needed. Thank you!

"Ragdyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ncspndoc" wrote in message
...
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.

"Gary''s Student" wrote:

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
--
Gary's Student


"ncspndoc" wrote:

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.