Create new set of numbers based on conditional rules

why man, your problem was to repeat a charcter starting from 1,2,3...35 times per each cell. this is another problem. :S

Originally Posted by shaukat

why man, your problem was to repeat a charcter starting from 1,2,3...35 times per each cell. this is another problem. :S

Bro. shaukat,

as i mentioned in my earlier post in the other thread, i have wrongly stating my question there. And you are right when interpretating that question. I consider my question there has been answered.

Now, in this thread i have composed my question again to give clear picture on the situation that i'm currently need to solve it. Appreciate your help or any other members here who could give a hand on this matter.

For additional info : I'm doing a project whereby i need to convert any words (max of 35 characters) into numbers (each alphabet is assign with certain number). From the numbers generated, i need to do some calculation based on sets of rules as mentioned above.

Originally Posted by shaukat

for example, take first case:

2,1,3,4,9,8,10,1,2,7,7,8,5,3,4,2,9,7,8,11,8,6,5,9,2,1,1,8,7,6,5,3,2,1,8
result : 3,7,17,10,1,9,15,8,6,16,8,11,14,14.......... is this ok?

The result should be : 3,7,17,10,3,14,13,7,11,15,11,14,14..........

Explaination :

2 & 1 (both are less than 10 individually), so 2 + 1 = 3,

the next no. is 3 and since no 4 is next to it also less than 10, 3 + 4 = 7,

after that is no 9 and no 8 (both are also less than 10 individually), so 9 + 8 = 17,

right after that is no 10 and no 1, since only 1 number is less than 10, no. 10 remain,

continue again with no. 1 and next to it is no 2 (both are less than 10 individually, so 1 + 2 = 3,

and so on....


Rules: as long as the 2 numbers adjacent are less than 10, it will be added together or else it will remain as it is..

Originally Posted by royUK

How is Conditional Formatting involved?

royUK,

thanks for the question. I just realised that no conditional formatting involved.

I have changed the title. It should be "Create new set of numbers based on conditional rules" not "Create new set of numbers with conditional format".

Originally Posted by nilem

Maybe so? 1st row on the sheet - the original set, 2nd row - the result.

Please Login or Register  to view this content.

Thank you very much nilem. Excellent! This is what i need.

@ Nilem very good script , however
series: 1 2 4 10 1 5 7 10 1 11 3 12 1
result: 3 4 10 6 7 10 1 11 3 12 1
the last 3 and 1 should be summed up. what is your opinon?
result: 3 4 10 6 7 10 1 11 12 4

Hi Shaukat
I proceeded from the fact that

Any 2 adjacent numbers in the row in the first set will be added together...

Originally Posted by nilem

Hi Shaukat
I proceeded from the fact that

Yes nilem. You've got it right.

nilem,

if you don't mind, how about to set the result to minimum of 4 digit. The operation stops whenever the end result equals to 4 digits.

Example :

First Set : 2,1,3,4,9
Result : 3,3,4,9

Thanks.

If understood correctly ... Try to add the red line

Please Login or Register  to view this content.

Originally Posted by nilem

If understood correctly ... Try to add the red line

Please Login or Register  to view this content.

nilem,

here are examples to explain the expected results:

Example 1:

First Set : 2,1,3,4,9 ==> no's 2 & 1 are added together, the remaining 3 no's remain
Result : 3,3,4,9 ==> the result must be minimum 4 digits


Example 2:

First Set : 21,3,4,9 ==> since there are only 4 digits, no need to add the numbers
Result : 21,3,4,9 ==> the result must be minimum 4 digits


Example 3:

First Set : 9,3,4,9 ==> since there are only 4 digits, no need to add the numbers
Result : 9,3,4,9 ==> the result must be minimum 4 digits


Note: The user are allowed to input numbers from 4 - 35 digits only in the fisrt set row.

Thanks for your help.

Example 4:

First Set : 21,3,4,9,1
Result : 21,7,9,1 ==> the result must be minimum 4 digits


Example 5:

First Set : 21,3,4,9,1,5,4
Result : 21,7,10,9 ==> the result must be minimum 4 digits


Example 6:

First Set : 3,4,19,1,19
Result : 7,19,1,19 ==> the result must be minimum 4 digits


Example 7:

First Set : 1,4,6,1,19
Result : 5,6,1,19 ==> the result must be minimum 4 digits

Try this:

Please Login or Register  to view this content.

Originally Posted by nilem

Try this:

Please Login or Register  to view this content.

I don't know how to thank you nilem. This code works perfectly. I have tested it for the below scenario :

The input for numbers to be evaluated start from cell A1. After I executed the macro based on the code given, the result is generated start in cell C2 onwards.

What if I need to put the numbers for the evaluation start in cell D12 and the result generated start in cell D13?

Million thanks to you.

Originally Posted by promo786

What if I need to put the numbers for the evaluation start in cell D12 and the result generated start in cell D13?

Write these lines

Please Login or Register  to view this content.

instead of these lines
'With [c1].CurrentRegion
' a = .Rows(1).Resize(, .Columns.Count + 1).Value
'End With: uba = UBound(a, 2): ReDim b(1 To uba)
And write this line

Please Login or Register  to view this content.

instead of the
'[c2].Resize(, uba).Value = b

Originally Posted by nilem

Write these lines

Please Login or Register  to view this content.

instead of these lines
'With [c1].CurrentRegion
' a = .Rows(1).Resize(, .Columns.Count + 1).Value
'End With: uba = UBound(a, 2): ReDim b(1 To uba)
And write this line

Please Login or Register  to view this content.

instead of the
'[c2].Resize(, uba).Value = b

Million thanks nilem

It works!

Hi all,

Anyone here could help me to solve the problem i'm facing here? I have successfully used the codes given to achieve my target however another problem occurred.

Actually the original set of numbers that i wanted to convert based on the conditional rules are derived from a word via few excel formula/processes. After the word is converted to the relevant numbers, i ran a macro to convert it. Unfortunately, for certain words it failed to generate the correct result. (Refer to the file attached in tab "Kiraan").

However, if i type in the same set numbers manually (not involving any excel formula/processes), the result generated after i ran the macro is correct. (Refer to the file attached in tab "Nama")

Thanks in advance.

Try it (see attachment).

Thank again nilem. This is awesome. You have made the macro run automatically instead of manually executed.

Referring to the code in the file :

Please Login or Register  to view this content.

What is "-4123" is referring to / how do you get the no.?

it means

Please Login or Register  to view this content.

Look the Help for VBA