Need Help To Find Out The Input

[ranjith2523]

Hi Experts,

In the below Sub test2() code if I input "Hash ("acdegilmn")" then I am getting the output message as "909832457685751".

What input I should give if I need to get the output value as "945901726134069". I tried changing the input value multiple times but I am not getting the output value as "945901726134069".
Can someone help me to get the output value "945901726134069" by changing the input value in Sub test2() code ?

Sub Hash(inputString)

Dim outputNumber As Double

outputNumber = 7

Dim letters As String

letters = "acdegilmnoprstuw"

Dim index As Long
index = 0
Dim lengthOfInputString As Double

lengthOfInputString = Len(inputString)

While (index < lengthOfInputString)

outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1))) - 1)
index = index + 1

Wend
MsgBox (outputNumber)
End Sub

Sub test2()

Hash ("acdegilmn")

End Sub

 

[GSP75]

2/2
Для включення назви списку до шаблону вам потрібно додати рядок коду, щоб скопіювати це значення на відповідній аркуші шаблону перед збереженням копії. Це можна зробити за допомогою наступного рядка коду:
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
Після цього коду буде виглядати наступним чином:
Sub SaveMasterAs()
'Навчальний посібник Excel 10
Dim wb As Workbook
Dim rНазви як розділ, c як розділ, r як розділ
'Список імен та ідентифікаторів поточного файлу на аркуші1.
Встановіть rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)) 'Шлях і ім'я до основної книги, яку потрібно відкрити
для копіювання, зберегти.
Встановіть wb = Workbooks.Open(ThisWorkbook.Path & "\Template.xlsx")
Для кожного c In rNames
З wb
'Якщо вам потрібно скопіювати щось до шаблону, скористайтеся цим рядком
.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
'Шлях і ім'я для скопійованої книги
.SaveAs Filename:=ThisWorkbook.Path & "\Template Copy" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Закінчити з
Встановіть wb = ActiveWorkbook
Далі c
wb.Закрити
Кінець Під
Перегляньте, що на аркуші1 у вас є стовпець A, у якому співпадають назви, а стовпець B - ідентифікатори. Також переконайтеся, що на аркуші шаблону є клітинка A1, де ви можете скопіювати назву зі списку. Збережіть цей файл як Template.xlsx у папці, де ви збережете копії шаблону.

 

[ranjith2523]

2/2
Для включення назви списку до шаблону вам потрібно додати рядок коду, щоб скопіювати це значення на відповідній аркуші шаблону перед збереженням копії. Це можна зробити за допомогою наступного рядка коду:
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
Після цього коду буде виглядати наступним чином:
Sub SaveMasterAs()
'Навчальний посібник Excel 10
Dim wb As Workbook
Dim rНазви як розділ, c як розділ, r як розділ
'Список імен та ідентифікаторів поточного файлу на аркуші1.
Встановіть rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)) 'Шлях і ім'я до основної книги, яку потрібно відкрити
для копіювання, зберегти.
Встановіть wb = Workbooks.Open(ThisWorkbook.Path & "\Template.xlsx")
Для кожного c In rNames
З wb
'Якщо вам потрібно скопіювати щось до шаблону, скористайтеся цим рядком
.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
'Шлях і ім'я для скопійованої книги
.SaveAs Filename:=ThisWorkbook.Path & "\Template Copy" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Закінчити з
Встановіть wb = ActiveWorkbook
Далі c
wb.Закрити
Кінець Під
Перегляньте, що на аркуші1 у вас є стовпець A, у якому співпадають назви, а стовпець B - ідентифікатори. Також переконайтеся, що на аркуші шаблону є клітинка A1, де ви можете скопіювати назву зі списку. Збережіть цей файл як Template.xlsx у папці, де ви збережете копії шаблону.I

I am sorry, i do not understand this language, even if I translate I understand that this response is not relevant to my question. Can some please help me on this.

 

[smozgur]

In the original form of the code, I can tell there is no solution. (Ok, at least I can't see any solution)

However, this line in your code:

outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1))) - 1)

Is it possible that this line is supposed to be like the following (Mid function with the third argument)?

outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1), 1)) - 1)

If this could be the case then there is a working solution: "pramodena" is the combination that returns the number you expected. Note: I am not sure if another combination could be generated by also using some letters which do not exist in the "letters".

 

[ranjith2523]

In the original form of the code, I can tell there is no solution. (Ok, at least I can't see any solution)

However, this line in your code:


Is it possible that this line is supposed to be like the following (Mid function with the third argument)?

outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1), 1)) - 1)

If this could be the case then there is a working solution: "pramodena" is the combination that returns the number you expected. Note: I am not sure if another combination could be generated by also using some letters which do not exist in the "letters".

Perfect !!! Yes "pramodena" brings the output as "945901726134069". Please please please help me to understand what is the calculation logic behind this, how you quickly identify the output value "945901726134069". One of colleague applied this same "pramodena" in below excel to get the input value as "945901726134069". My only confusion is how to identify the "pramodena" to bring the output value
"945901726134069".

Book2.xlsx
	A	B	C	D	E
1	acdegilmnoprstuw		7
2		P	269	37	10
3		r	9964	37	11
4		a	368668	37	0
5		m	13640723	37	7
6		o	504706760	37	9
7		d	18674150122	37	2
8		e	690943554517	37	3
9		n	25564911517137	37	8
10		a	945901726134069	37	0
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=(C1*D2)+E2
E2:E10	E2	=SEARCH(B2,$A$1)-1

 

[smozgur]

It is basically reversing the formula used and starting from the result. I started from the target number and went one step back by testing each step. I tested each step by using numbers from 1 to 16 (index in the key) until finding a whole number. Of course, I used Excel cells that makes this process easier than it normally could be, and the numbers were limited, so it was not complicated than it is which is usually a very complicated process.

The most important part for you here I think, your formula was wrong as I explained. I am sure anybody can get the same result with the correct formula quickly.

 

[ranjith2523]

It is basically reversing the formula used and starting from the result. I started from the target number and went one step back by testing each step. I tested each step by using numbers from 1 to 16 (index in the key) until finding a whole number. Of course, I used Excel cells that makes this process easier than it normally could be, and the numbers were limited, so it was not complicated than it is which is usually a very complicated process.

The most important part for you here I think, your formula was wrong as I explained. I am sure anybody can get the same result with the correct formula quickly.

You explained clearly but I am not getting this based on your above sentence I need to start working from the result and I need to reverse the formula.
Can you give me a small example at least the first two steps of how you did this? I started to do something like the below and I am really not sure what data I should apply on columns B and D. It is really confusing for me. If you don't mind can you explain at least one or two steps? Really sorry to bother you.
I understand we should start apply the formula from the output value "945901726134069" e.g. 945901726134069/37 = 25564911517137, then 25564911517137/37,.. so on but not sure how to find the "promodena" input with this.

Book2.xlsx
	A	B	C	D	E
1	acdegilmnoprstuw		7
2				37	0
3				37	0
4				37	0
5				37	0
6				37	0
7				37	0
8				37	0
9				37	0
10			945901726134069	37	0
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=SEARCH(B2,$A$1)-1

 

[smozgur]

It is not a good way to ask the same question twice. Or you can at least refer to the other question if they are related, so we can take a look at it to not duplicate what has been done already done, and help better without wasting duplicate time. I am referring to this question (which actually contains much more information than this thread):

Logical Thinking Question

Hi Friends, This question is not related to Excel or VBA but my senior told that the peoples who have knowledge on VBA can answer this question. I have little VBA knowledge but I didn't try to find solution for his question. Can someone please help me to understand the below question (also...

www.mrexcel.com

The following is how my approach to solving this problem. Basically, starting from the final value and applying the reversed formula. So, I started from C4, entered numbers between 1 and 16, and continue with C5 once I got TRUE in B4 which means I got the expected number. When I completed testing all at C12, I found the corresponding letters for the index numbers in C4:C12 and pull the corresponding letter by those indexes from the letters variable. Once I get the letters, I reverse them (since I started from the beginning), and finally, I got the result.

You can do the same by copying the following mini-sheet into your worksheet, deleting C4:C12 and starting entering numbers from C4 as I tried to explain.

Hope this makes sense.

1231821.xlsx
	A	B	C	D	E	F	G	H
1	acdegilmnoprstuw
2
3	945901726134069	Test	Position	Letter
4	25564911517137	TRUE	1	a
5	690943554517	TRUE	9	n
6	18674150122	TRUE	4	e
7	504706760	TRUE	3	d
8	13640723	TRUE	10	o
9	368668	TRUE	8	m
10	9964	TRUE	1	a
11	269	TRUE	12	r
12	7	TRUE	11	p
13
14	Position	Test numbers from 1 to 16
15	Test	Make sure the generated result by using the previous number is a whole number
16	Letter	Use MID function to get the letter in the found position
17	Result (reverse)	pramodena
Sheet1

Cell Formulas
Range	Formula
A4:A12	A4	=(A3+1-C4)/37
B4:B12	B4	= ROUND(A4,0)=A4
D4:D12	D4	=MID(A$1,C4,1)
B17	B17	=TEXTJOIN("",,MID(TEXTJOIN("",, D4:D12),SEQUENCE(,9,9,-1),1))

 

[ranjith2523]

It is not a good way to ask the same question twice. Or you can at least refer to the other question if they are related, so we can take a look at it to not duplicate what has been done already done, and help better without wasting duplicate time. I am referring to this question (which actually contains much more information than this thread):

Logical Thinking Question

Hi Friends, This question is not related to Excel or VBA but my senior told that the peoples who have knowledge on VBA can answer this question. I have little VBA knowledge but I didn't try to find solution for his question. Can someone please help me to understand the below question (also...

www.mrexcel.com

The following is how my approach to solving this problem. Basically, starting from the final value and applying the reversed formula. So, I started from C4, entered numbers between 1 and 16, and continue with C5 once I got TRUE in B4 which means I got the expected number. When I completed testing all at C12, I found the corresponding letters for the index numbers in C4:C12 and pull the corresponding letter by those indexes from the letters variable. Once I get the letters, I reverse them (since I started from the beginning), and finally, I got the result.

You can do the same by copying the following mini-sheet into your worksheet, deleting C4:C12 and starting entering numbers from C4 as I tried to explain.

Hope this makes sense.

1231821.xlsx
	A	B	C	D	E	F	G	H
1	acdegilmnoprstuw
2
3	945901726134069	Test	Position	Letter
4	25564911517137	TRUE	1	a
5	690943554517	TRUE	9	n
6	18674150122	TRUE	4	e
7	504706760	TRUE	3	d
8	13640723	TRUE	10	o
9	368668	TRUE	8	m
10	9964	TRUE	1	a
11	269	TRUE	12	r
12	7	TRUE	11	p
13
14	Position	Test numbers from 1 to 16
15	Test	Make sure the generated result by using the previous number is a whole number
16	Letter	Use MID function to get the letter in the found position
17	Result (reverse)	pramodena
Sheet1

Cell Formulas
Range	Formula
A4:A12	A4	=(A3+1-C4)/37
B4:B12	B4	= ROUND(A4,0)=A4
D4:D12	D4	=MID(A$1,C4,1)
B17	B17	=TEXTJOIN("",,MID(TEXTJOIN("",, D4:D12),SEQUENCE(,9,9,-1),1))

First, I would like to say sorry to ask the same question in different ways in two threads. Please note that I initially raised the question in C# language in one thread and i haven't received a response at that time then i realize that this forum is specific to Excel and i shouldn't have posted C# queries. Then i converted it into Excel VBA query and posted in a new thread, later i found that i have received a response for C# thread.

I forgive my ignorance, I will not repeat this mistake in my future threads. I shouldn't have wasted many people's threads who are helping other people. Sorry again. Thanks for your help. I will try to delete the duplicate thread or mark it as complete.

Thanks for all your help and support.

 

[smozgur]

No need to be sorry and there is nothing to be forgiven. I am just trying to explain if we knew all the information in the other thread, then we could have helped much faster and without assumptions, even if the assumptions were right. No need to delete the other thread since I already provided a link to it. And actually, it also has the answer in C with the explanation. I would mark the answer as the solution in that thread by the way. Thanks for understanding the duplicate thread issue.

Glad to hear it helps. (I am assuming that I was able to explain how I found the solution above. If there is any question on it, just let me know.)