How to automatically fill a column?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I express this?

Assume 2 columns, A and B.
In A, a col of numbers: 1, 2, 3, etc.
In B, now empty, I need to have a col of words that describe in words
what the numbers stand for: apples (for No 1), oranges (for No2),
lemons (for No 3), etc.

I already have the col with the numbers, Column A. I need a way to
tell Excel to fill in col B with the word "apple" wherever it sees a
"1" in col A. And to fill in col B with the word "oranges" whereever
it sees the number "2" in col A. etc, etc.

Is this do-able?

Best Regards,

Arthur
 
Think VLOOKUP would be simplest way ..

Set-up the reference table, say in Sheet2's A1:B3

1 apple
2 oranges
3 lemons
(etc)

Then in say, Sheet1, if the numbers are listed in A1 down,
you could put in B1: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
and just copy B1 down

Alternatively, we could also have a "standalone" reference table within the
VLOOKUP (provided it's just a small table - from the maintenance angle)

Try also in say, C1:
=IF(A1="","",VLOOKUP(A1,{1,"apple";2,"oranges";3,"lemons"},2,0))
and copy C1 down

Both cols B & C would return the same results for the sample table
 
The best way to go depends on how many variables you have, and if you're
*really* using 1, 2, 3, ... etc.

For example:

=CHOOSE(A1,"Apples","Oranges","Lemons","Pears","Plums")

Will return either of these 5 items if A1 contains either number, from 1 to
5.

If your slate of items is larger, you may find a data list easier to manage,
in conjunction with something like one of the lookup functions.

Post back if you would like to try another option.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
In the simplest form a formula -
=If(a1=1,"apples",if(a1=2,"oranges",if(a1=3,"lemons",""))) [I hope you can
see how to extend this formula]

However, you are limited to a certain number of arguments in a formula. In
that case you will need an additional column for the additional arguments
and a fourth column to pick up the output of the previous two into a single,
usable, column for whatever your purpose.

Regards.

Bill Ridgeway
Computer Solutions
 
Thank You All

for your views.

Since the number of items seems to be important, I will give you an
idea of the size.

I have about 300 separate number codes to translate into descriptive
words and short phrases. This in many thousands of records contained
in a few dozen databases. Not the kind of thing that I can do by hand
in one lifetime.

Under these conditions, what do you think is the best way to go?

Best Regards,

Arthur

******************************************************
 
IMHO (.. not because it's in my response <g>),
use VLOOKUP with the lookup reference table
created in a separate sheet (Sheet2's cols A & B),
and then use a formula such as:
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
 
For that large a number of items, I have to agree with Max that a data list
and one of the Lookup functions is the way to go.
 
Thank you all for you answers.

I would never have thought of Vlookup and ref tables on my own.

Best Regards
 
Back
Top