Lexicographic number help.

[serge]

Hi everyone,

I need someone help to find a combination of 5 numbers FROM a Lexicographic number ?

In cell A1:E1 I have those numbers : 1-2-3-4-5.
In cell : G1 I have this formula :

=COMBIN(56,5)-IF(52-A1>0,COMBIN(56-A1,5),0)-IF(53-B1>0,COMBIN(56-B1,4),0)-IF(54-C1>0,COMBIN(56-C1,3),0)-IF(55-D1>0,COMBIN(56-D1,2),0)-IF(56-E1>0,COMBIN(56-E1,1),0)

Which give me in return the Lexicographic 1 for that combination.

If I change my numbers from A1:E1 to 52-53-54-55-56.

G1 would return the Lexicographic : 3 819 816.

Now my question is :

I need it the other way around, which formula should be use in A1:E1, if I TYPE in G1 the Lexicographic number ???

Thank you in advance.
Serge.

 

[ZVI]

Try UDF function:

' Assuming that Lexicographic number is in G1, do as follows:
' 1. Select the resulting cells, for example A1:E1,
' 2. Type formula: =LexToNums(G1)
' 3. Confirm by Ctrl-Shift-Enter
Function LexToNums(LexVal As Long)
  Dim a&, b&, c&, d&, e&, v&
  If LexVal < 1 Or LexVal > 3819816 Then Exit Function
  For a = 1 To 52
    For b = a + 1 To 53
      For c = b + 1 To 54
        For d = c + 1 To 55
          For e = d + 1 To 56
            v = v + 1
            If v = LexVal Then
              LexToNums = Array(a, b, c, d, e)
              Exit Function
            End If
          Next
        Next
      Next
    Next
  Next
End Function

 

[serge]

ZVI,

Thank you for the macro but I need a formula for this job.
And also I don't know how to use macro !!!

Thanks for your time.
Serge.

 

[ZVI]

Serge, hope you’ll find the formula solution.
But the problem is rather in algorithm than in implementation, so your description of the algorithm is desired.

My algorithm is the simple enumeration.
Ok, the information below is just for the case.

First of all Enable macros.
Then do as follows:
1. Select the code of post#2 and press Crtl-C to put it in the clipboard
2. Press Alt-F11 to open VBE
3. Choose menu Insert – Module to add VBA Module1 into workbook
4. Press Ctrl-V to copy code from the clipboard into Module1
5. Press Alt-Q to close VBE
6. Put in G1 the Lexicographic number you want to decode
7. Select 5 cells in a row, for example A2:E2
8. Type the formula: =LexToNums(G1)
9. Confirm by Ctrl-Shift-Enter as for the array formula

The layout and formulas:

Excel Workbook
	A	B	C	D	E	F	G
1	52	53	54	55	56		3819816
2	52	53	54	55	56
3
Sheet

Regards

 

[serge]

Thank you very much ZVI for your details explanations on how to use the macro, and also for your time, it works fine.

I would have like a formula to do the job also, just because I would be able to use it somewhere else for my data.

Again Thank you, I really been helpful.

Serge.