Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a drop down button on a worksheet? | Excel Worksheet Functions | |||
How to Create a Macro to Edit a Variable Amount of Information | New Users to Excel | |||
Create a tabular control | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
I Need VBA Assistance for global variable question | Excel Discussion (Misc queries) |