create new variable
 

I have a list of names, 10 of which I want to keep as is and the others I
want to change to "all other" (there are too many to use find & replace).
Can I create a new column and new variable to do this?

Thanks,

create new variable
 

Hi Angie,

You could list the 10 names you want to keep, for example in C1:C10.
Then in B1 enter this formula:

=IF(ISNA(VLOOKUP(A1,C$1:C$10,1,0)),"All Other",A1)

and copy down column B by double-clicking the fill-icon (the small
black square in the bottom corner of the cursor. This assumes that your
names are in column A.

You could then highlight all the cells with the formula, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc in order to
fix the values.

Hope this helps.

Pete

Angie wrote:

I have a list of names, 10 of which I want to keep as is and the others I
want to change to "all other" (there are too many to use find & replace).
Can I create a new column and new variable to do this?

Thanks,

create new variable
 

Assuming the source names are in A1 down

Enter/list your 10 names to retain in B1:B10

Put in C1:
=IF(ISNUMBER(MATCH(A1,B:B,0)),A1,"All Other")
Copy C1 down to the last row of data in col A,
to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Angie" wrote:
I have a list of names, 10 of which I want to keep as is and the others I
want to change to "all other" (there are too many to use find & replace).
Can I create a new column and new variable to do this?

Thanks,

create new variable
 

Try something like this:

With
Your list of all names in Col_A, beginning in A2
and
N1:N10 contains the list of names you want to "keep"

Try this in Col_B:
B2: =IF(COUNTIF($N$1:$N$10,A2),A2,"All Other")
Copy that formula down as far as you need

Then....just
select the Col_B formula cells
[Ctrl]+C......that's the shortcut for <edit<copy
<edit<paste special<values

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Angie" wrote:

I have a list of names, 10 of which I want to keep as is and the others I
want to change to "all other" (there are too many to use find & replace).
Can I create a new column and new variable to do this?

Thanks,

create new variable
 

you may also use OTHER ways,

your 10 names in A1:J1

=+IF(SUM((A2=$A$1:$J$1)*{1}),A2,"ALL OTHERS")
hit ctrl-shift-ent..

or =+IF(MAX((A2=$A$1:$J$1)*{1}),A2,"ALL OTHERS")
hit ctrl-shift-ent..
--
*****
birds of the same feather flock together..



"Ron Coderre" wrote:

Try something like this:

With
Your list of all names in Col_A, beginning in A2
and
N1:N10 contains the list of names you want to "keep"

Try this in Col_B:
B2: =IF(COUNTIF($N$1:$N$10,A2),A2,"All Other")
Copy that formula down as far as you need

Then....just
select the Col_B formula cells
[Ctrl]+C......that's the shortcut for <edit<copy
<edit<paste special<values

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Angie" wrote:

I have a list of names, 10 of which I want to keep as is and the others I
want to change to "all other" (there are too many to use find & replace).
Can I create a new column and new variable to do this?

Thanks,