Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka ... ... so, could you please provide that program. Thanks & Regards, Venkatesh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
Check your look up table. You should have each letter listed only once. You
are missing the Letter B and have the letter D twice. "Venkatesh V" wrote: HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
Copy this UDF to a general module
If the first name is in A1, then in B1 enter =TRYME(A1) Copy down the column Please note I have used BMT worth 4 and DKR worth 2 as you had omitted B and included two Ds Change as needed Function Tryme(mycell) mylen = Len(mycell) For j = 1 To mylen mytest = UCase(Mid(mycell, j, 1)) If InStr(1, "AIJQY", mytest) 0 Then mysum = mysum + 1 ElseIf InStr(1, "DKR", mytest) 0 Then mysum = mysum + 2 ElseIf InStr(1, "CGLS", mytest) 0 Then mysum = mysum + 3 ElseIf InStr(1, "BMT", mytest) 0 Then mysum = mysum + 4 ElseIf InStr(1, "EHNX", mytest) 0 Then mysum = mysum + 5 ElseIf InStr(1, "UVW", mytest) 0 Then mysum = mysum + 6 ElseIf InStr(1, "FP", mytest) 0 Then mysum = mysum + 7 ElseIf InStr(1, "OZ", mytest) 0 Then mysum = mysum + 8 End If Next j Tryme = mysum End Function best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Venkatesh V" wrote in message ... HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
On Tue, 24 Feb 2009 04:43:01 -0800, Venkatesh V
wrote: HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh Desired output examples are always useful. If I understand you correctly, you would want to convert a string like FO to 7+8 or 15. Is that correct? That being the case: 1. What about <space? Should that be ignored (or equivalent to zero)? 2. What about the letter "D" which you show twice? For the sake of this example, I assumed the second D (in DMT) was in error and deleted it. I also assumed that <space = 0 and that upper case and lower case letters were to be treated the same. That being the case, you could download and install Longre's free morefunc.xll add-in (do a Google search for a source, as the original web site source seems to be intermittent), and then use this formula (with the string in A1): =EVAL(LEFT(SETV(REGEX.SUBSTITUTE(A1, "([AIJQY])|([DKR])|([CGLS])|([MT])|([EHNX])|([UVW])|([FP])|([OZ])|\s", "[1=1+,2=2+,3=3+,4=4+,5=5+,6=6+,7=7+,8=8+]",,,FALSE)),LEN(GETV())-1)) If you would prefer to use a User Defined Function: To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this User Defined Function (UDF), enter a formula like =SumLtrs(cell_ref) in some cell. ================================================== == Option Explicit Option Compare Text Function SumLtrs(str As String) As Double Dim i As Long Dim dTemp As Double For i = 1 To Len(str) Select Case Mid(str, i, 1) Case "A", "I", "J", "Q", "Y" dTemp = dTemp + 1 Case "D", "K", "R" dTemp = dTemp + 2 Case "C", "G", "L", "S" dTemp = dTemp + 3 Case "M", "T" dTemp = dTemp + 4 Case "E", "H", "N", "X" dTemp = dTemp + 5 Case "U", "V", "W" dTemp = dTemp + 6 Case "F", "P" dTemp = dTemp + 7 Case "O", "Z" dTemp = dTemp + 8 End Select Next i SumLtrs = dTemp End Function ====================================== --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
Assuming Indian Numerology, the 'D' located in the "2" group is supposed to
be a 'B'. This formula will produce the sum you want (it treats upper and lower case letters the same)... =SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1 ), " AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5)) -- Rick (MVP - Excel) "Venkatesh V" wrote in message ... HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
=SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1 ),
" AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5)) By the way... don't modify the spacing in any way... I know it looks funny, but the exact spacing I have used is critical to the proper functioning of the formula. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Assuming Indian Numerology, the 'D' located in the "2" group is supposed to be a 'B'. This formula will produce the sum you want (it treats upper and lower case letters the same)... =SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1 ), " AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5)) -- Rick (MVP - Excel) "Venkatesh V" wrote in message ... HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name number program
excellent work. THanks a ton "Rick Rothstein" wrote: =SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1 ), " AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5)) By the way... don't modify the spacing in any way... I know it looks funny, but the exact spacing I have used is critical to the proper functioning of the formula. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Assuming Indian Numerology, the 'D' located in the "2" group is supposed to be a 'B'. This formula will produce the sum you want (it treats upper and lower case letters the same)... =SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1 ), " AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5)) -- Rick (MVP - Excel) "Venkatesh V" wrote in message ... HI, I require a macro / program or excel based tool -- which gives the total number based on the numbers assigned to each letter. here are the number assignation to letters. AIJQY = 1 DKR = 2 CGLS = 3 DMT = 4 EHNX = 5 UVW = 6 FP = 7 OZ = 8 My requirement is -- i have some 50 names. copy all the names in a column, run macro or function that will automatically populate the sum of numbers in each letter of the names (in next column) i have tried it. but its geting difficult and time consuming. errors are creeping up for this simpe program! For example names could be A Varsha G Sanya V Viveka .. .. so, could you please provide that program. Thanks & Regards, Venkatesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i delete a program that converts number into letters | Excel Discussion (Misc queries) | |||
How to program an auto incrementable number in a cell, at each ope | Excel Programming | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) | |||
Apply Excel Program to a Number of Rows | Excel Programming | |||
merging excel program with tdc finance program | Excel Programming |