how to use functions.

[Guest]

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.

 

[Guest]

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

 

[Sandy Mann]

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","Pakistan","Burma","Scotlans","Englans","Ireland","Wales")

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you Sandy again.
i corrected accordingly.

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk