how to use functions.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sompura - 15 Sep 2007 13:28 GMT

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 to this Message

Gary''s Student - 15 Sep 2007 14:16 GMT

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

Signature

Gary''s Student - gsnu200744

> 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
[quoted text clipped - 3 lines]
>
> I will be thankful for your valuable suggession.

Reply to this Message

sompura - 16 Sep 2007 13:52 GMT

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.

> Say our data looks like:
>
[quoted text clipped - 56 lines]
> >
> > I will be thankful for your valuable suggession.

Reply to this Message

Sandy Mann - 16 Sep 2007 14:35 GMT

> 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)

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Yes
> thank you
[quoted text clipped - 66 lines]
>> >
>> > I will be thankful for your valuable suggession.

Reply to this Message

sompura - 16 Sep 2007 15:02 GMT

Thank you Sandy again.
i corrected accordingly.

> > thank you
> > it worked provided the data in col B is in ascending order
[quoted text clipped - 73 lines]
> >> >
> >> > I will be thankful for your valuable suggession.

Reply to this Message

sompura - 20 Sep 2007 10:30 GMT

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.

> > thank you
> > it worked provided the data in col B is in ascending order
[quoted text clipped - 73 lines]
> >> >
> >> > I will be thankful for your valuable suggession.

Reply to this Message

Sandy Mann - 20 Sep 2007 11:52 GMT

> 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.?

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> thank you
> if i want to get the data of column B and C from another file at other
[quoted text clipped - 84 lines]
>> >> >
>> >> > I will be thankful for your valuable suggession.

Reply to this Message

Sandy Mann - 20 Sep 2007 11:54 GMT

To Gary"s Student:

Sorry for hijacking your thread, I did not notice that sompura was replying
to you and not me.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>> thank you
>> it worked provided the data in col B is in ascending order
[quoted text clipped - 75 lines]
>>> >
>>> > I will be thankful for your valuable suggession.

Reply to this Message

Sandy Mann - 15 Sep 2007 14:29 GMT

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.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>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
[quoted text clipped - 4 lines]
>
> I will be thankful for your valuable suggession.

Reply to this Message