| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use functions.
I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in
Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
Say our data looks like:
7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
There is no function that can change the cell with the data in it. I assume
that the numbers in Column A are different to the numbers in Column B otherwise you could just copy and paste Column C over Column A. You could - on a copy of the sheet just in case - highlight Column A and select Edit Replace, enter one of your numbers in "Find what:" and the corresponding country in "Replace with:" Otherwise you will need a helper column - say Column D - and enter the formula: =CHOOSE(A1,"America","Brazil","China","India","Pak istan","Burma","Scotlans","Englans","Ireland","Wal es") Then copy Column D and Paste Special over the top of Column A Post back if you want something different. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
Yes
thank you it worked provided the data in col B is in ascending order, if not, result is changed. again thanks for your kind help. "Gary''s Student" wrote: Say our data looks like: 7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
Thank you Sandy again.
i corrected accordingly. "Sandy Mann" wrote: sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order Ooops! sorry that was my fault make the formual: =VLOOKUP(A1,$B$1:$C$19,2,FLASE) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order, if not, result is changed. again thanks for your kind help. "Gary''s Student" wrote: Say our data looks like: 7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
thank you
if i want to get the data of column B and C from another file at other location then waht should i do? hope for your kind reply. "Sandy Mann" wrote: sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order Ooops! sorry that was my fault make the formual: =VLOOKUP(A1,$B$1:$C$19,2,FLASE) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order, if not, result is changed. again thanks for your kind help. "Gary''s Student" wrote: Say our data looks like: 7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
"sompura" wrote in message
... if i want to get the data of column B and C from another file at other location then waht should i do? hope for your kind reply. I don't understand what it is that you want, can you please explain what it is that you are trying to do a bit more.? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... thank you if i want to get the data of column B and C from another file at other location then waht should i do? hope for your kind reply. "Sandy Mann" wrote: sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order Ooops! sorry that was my fault make the formual: =VLOOKUP(A1,$B$1:$C$19,2,FLASE) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order, if not, result is changed. again thanks for your kind help. "Gary''s Student" wrote: Say our data looks like: 7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to use functions.
To Gary"s Student:
Sorry for hijacking your thread, I did not notice that sompura was replying to you and not me. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order Ooops! sorry that was my fault make the formual: =VLOOKUP(A1,$B$1:$C$19,2,FLASE) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "sompura" wrote in message ... Yes thank you it worked provided the data in col B is in ascending order, if not, result is changed. again thanks for your kind help. "Gary''s Student" wrote: Say our data looks like: 7 1 America 7 2 Brazil 7 3 Canada 2 4 Chile 9 5 England 3 6 France 10 7 Germany 10 8 Italy 10 9 Mexico 3 10 Spain 4 6 10 10 3 6 5 4 4 2 We want to replace the numbers in column A with the equivalent names in column C. In D1 enter: =VLOOKUP(A1,$B$1:$C$19,2) and copy down. We now see: 7 1 America Germany 7 2 Brazil Germany 7 3 Canada Germany 2 4 Chile Brazil 9 5 England Mexico 3 6 France Canada 10 7 Germany Spain 10 8 Italy Spain 10 9 Mexico Spain 3 10 Spain Canada 4 Chile 6 France 10 Spain 10 Spain 3 Canada 6 France 5 England 4 Chile 4 Chile 2 Brazil Finally take column D, copy it, and paste/special/values back onto column A -- Gary''s Student - gsnu200744 "sompura" wrote: I have 3 columns in Col. A numbers between 1 to 10 in nearly 400 raws, in Col.B Same numbers i.e. 1 to 10 which represents code of country shown in Col.C and in Col.C names of Country like America, Brazil, China, India, Pakistan, Barma etc. I want to replace Col. A's numbers with Country's name which will be appropriate function for replacement of numbers. I will be thankful for your valuable suggession. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula/functions for average and if functions | Excel Worksheet Functions | |||
| XL2003 FILTER FUNCTIONS VS. XL2007 FILTER FUNCTIONS | Excel Worksheet Functions | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
| Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
Linear Mode
