forming a new column by using the other column

[pinar]

Hi I have a question regarding to excel. I have a column includes numbers. It
is like;
Number:
10
10
7
7
23
55
40
I need specific numbers, lets say that, I only need number 10, 7 and 23 and
then I have to form a new column by using the data in the number column, for
example
If the number is 10 write AA
If the number is 7 write PM
If the number is 23 write HZ
And the other numbers can be deleted or can be 0
So at the end , I have to have something like that
Number Name
10 AA
10 AA
7 PM
7 PM
23 HZ
55 0
40 0
I have around 10 different numbers .Is there any body knows how to do it in
excel. better without writing any macro?
Thanks a lot

 

[Geovas]

Hi,
Assuming that your original data starts at A2 use
IF(A2=10,"AA",IF(A2=23,"HZ",IF(A2=7,"PM",0))) in cell B2 and then copy down
as needed.

This only works for this specific example so if you need to include more
numbers you will have to add more nested "IF" functions... Bear in mind that
you can only nest up to 7 "IF". In case you need more you will need to find
a different way...

Hope this helps...

 

[Arun Philip]

pinar,
I would recommend you use the VLOOKUP function to achieve the same.

Firstly, list the values and the values they must be mapped to, in your
worksheet. For example, starting from cell D15, enter in range D15:E:17:
10 AA
23 HZ
7 PM

Don't enter any values that do not have a map value. Then, if your numbers
are in column A, starting from 1, in cell B1 enter the formula:
=IF(ISNA(VLOOKUP(A1,$D$15:$E$17,2,FALSE)),0,VLOOKUP(A1,$D$15:$E$17,2,FALSE))

This formula does the following actions:
VLOOKUP(A1,$D$15:$E$17,2,FALSE)
looks up the value in cell A1 (first argument) within the first column of
the range D15:E17 (second argument). If an exact match (4th argument: if
FALSE looks for an exact match) is found, then the corresponding value in
column 2 (3rd argument) of the range is returned by the formula. If no match
is found, then it returns #N/A. This entire function is wrapped in IF() and
ISNA() functions, to determine whether to show 0 or the returned value.

HTH.

 

[pinar]

hi
thanks a lot for the replies.
first i tried to make it with if function but it gives error
i wrote this;
=IF(F3=334,"G",IF(F3=305,"A",IF(F3=320,"F,IF(F3=360,"H",IF(F3=362,"C",IF(F3=363,"P",IF(F3=580,"T",0)))))))

did i make something wrong?

 

[Arun Philip]

pinar,
soon after the place where you check F3=320, the "F" does not have a closing
double quote.

The corrected formula is:
=IF(F3=334,"G",IF(F3=305,"A",IF(F3=320,"F",IF(F3=360,"H",IF(F3=362,"C",IF(F3=363,"P",IF(F3=580,"T",0)))))))

I suggest you switch to a VLOOKUP function to reduce the complexity of this
formula. Further, using VLOOKUP helps you avoid hardcoding values into your
formula, but keeps it in an easily maintainable range.

 

[pinar]

Hi again
thank you but the thing is even with the if formula that u sent me ,
gives error. and i tried the vlookup but actually i am not so good a
excel and didnot get how to use it.
=IF(ISNA(VLOOKUP(A1,$D$15:$E$17,2,FALSE)),0,VLOOKUP(A1,$D$15:$E$17,2,FALSE))

You wrote that formula i undertood the first part but i didnot get wha
is difference in the second part of the formula. and when i wrote thi
formula it also gives error

 

[Geovas]

well i tried the formula that you used in excel, adding the "F" where you
only have "F and it worked... could you specify what error type it shows?
Also i agree with Arun on his use of the vlookup formula with the following
pointers...

Based on the criteria of the IF approach setup a table in range D15:E21
334 G

305 A

320 F

360 H

362 C

363 P

580 T

Then assuming your data starts at A1 use the vlookup formula at B1 and copy
down...

=IF(ISNA(VLOOKUP(A1,$D$15:$E$21,2,FALSE)),0,VLOOKUP(A1,$D$15:$E$21,2,FALSE))

 

[pinar]

thank you so much
somehow it works now :