|
forming a new column by using the other column
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 |
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... Ο "pinar" <pinar @discussions.microsoft.com έγραψε στο μήνυμα ... 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 |
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,VLOOKU P(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" wrote: 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 |
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=36 0,"H",IF(F3=362,"C",IF(F3=363,"P",IF(F3=580,"T",0) )))))) did i make something wrong?? -- pinar ------------------------------------------------------------------------ pinar's Profile: http://www.excelforum.com/member.php...o&userid=16067 View this thread: http://www.excelforum.com/showthread...hreadid=275198 |
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=3 60,"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" wrote: 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=36 0,"H",IF(F3=362,"C",IF(F3=363,"P",IF(F3=580,"T",0) )))))) did i make something wrong?? -- pinar ------------------------------------------------------------------------ pinar's Profile: http://www.excelforum.com/member.php...o&userid=16067 View this thread: http://www.excelforum.com/showthread...hreadid=275198 |
Hi again thank you but the thing is even with the if formula that u sent me , i gives error. and i tried the vlookup but actually i am not so good at excel and didnot get how to use it. =IF(ISNA(VLOOKUP(A1,$D$15:$E$17,2,FALSE)),0,VLOOKU P(A1,$D$15:$E$17,2,FALSE)) You wrote that formula i undertood the first part but i didnot get what is difference in the second part of the formula. and when i wrote this formula it also gives error. -- pinar ------------------------------------------------------------------------ pinar's Profile: http://www.excelforum.com/member.php...o&userid=16067 View this thread: http://www.excelforum.com/showthread...hreadid=275198 |
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,VLOOKU P(A1,$D$15:$E$21,2,FALSE)) Ο "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=36 0,"H",IF(F3=362,"C",IF(F3= 363,"P",IF(F3=580,"T",0))))))) did i make something wrong?? -- pinar ------------------------------------------------------------------------ pinar's Profile: http://www.excelforum.com/member.php...o&userid=16067 View this thread: http://www.excelforum.com/showthread...hreadid=275198 |
thank you so much somehow it works now :) -- pinar ------------------------------------------------------------------------ pinar's Profile: http://www.excelforum.com/member.php...o&userid=16067 View this thread: http://www.excelforum.com/showthread...hreadid=275198 |
| All times are GMT +1. The time now is 05:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com