• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pairs of numbers

B

bines53

Active Member
Hello friends ,

Number with 4 digits ,you have to find pairs,A formula that suits all 4 different situations
for example

The number ,1234 there are 6 pairs ,{12;13;14;23;24;34}

The number ,5668 there are 4 pairs ,{56;58;66;68}
The number ,5777 there are 2 pairs,{57;77}

The number ,7777 there are 1 pair, {77}

You can start the solution, from first row, or second row,

Conditioning is, without volatile functions ,And NO UDF !

David
 

Attachments

Why NO UDF?
This will be relatively simple with a UDF, extremely difficult as a formula ?
 
bines53 said:
Hello friends ,

Number with 4 digits ,you have to find pairs,A formula that suits all 4 different situations
for example

The number ,1234 there are 6 pairs ,{12;13;14;23;24;34}

The number ,5668 there are 4 pairs ,{56;58;66;68}
The number ,5777 there are 2 pairs,{57;77}

The number ,7777 there are 1 pair, {77}

You can start the solution, from first row, or second row,

Conditioning is, without volatile functions ,And NO UDF !

David
Click to expand...
 

Attachments

Hi Hui ,

I can not prevent you from solving with UDF ;)

I do not know if it is so difficult, not easy of course, with VBA, of course every solution is easy, I prefer formulas, also exposed to new things.
It does not matter, if the formula is short or long, it is important how it is built.

David
 
here is a UDF Solution

Code: 
Function Extract_Pairs(str As Variant, Optional sort As String = "None") As Variant

' Declare
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim i As Integer, j As Integer, k As Integer
Const sep As String = ";"

'Loop through input string and add pairs to collection
For i = 1 To Len(str)-1
  For j = i + 1 To Len(str)
    ' Add item
    If Not (dict.Exists(Mid(str, i, 1) & Mid(str, j, 1))) Then
      dict.Add Mid(str, i, 1) & Mid(str, j, 1), 1
    End If
  Next j
Next i

If sort = "xlAscending" Then
  Set dict = SortDictionaryByKey(dict, xlAscending)
ElseIf sort = "xlDescending" Then
  Set dict = SortDictionaryByKey(dict, xlDescending)
End If

'Extract elements out of Collection into string
For Each key In dict.keys
  tempstr = tempstr & key & sep
Next

'Return string to function
Extract_Pairs = "{" & Left(tempstr, Len(tempstr) - 1) & "}"


End Function

Public Function SortDictionaryByKey(dict As Object _
                  , Optional sortorder As XlSortOrder = xlAscending) As Object
 
 
  ' This SortDictionaryByKey function borrowed from Excel Macro Mastery
  ' https://excelmacromastery.com/

    Dim arrList As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
 
    ' Put keys in an ArrayList
    Dim key As Variant, coll As New Collection
    For Each key In dict
        arrList.Add key
    Next key
 
    ' Sort the keys
    arrList.sort
 
    ' For descending order, reverse
    If sortorder = xlDescending Then
        arrList.Reverse
    End If
 
    ' Create new dictionary
    Dim dictNew As Object
    Set dictNew = CreateObject("Scripting.Dictionary")
 
    ' Read through the sorted keys and add to new dictionary
    For Each key In arrList
        dictNew.Add key, dict(key)
    Next key
 
    ' Clean up
    Set arrList = Nothing
    Set dict = Nothing
 
    ' Return the new dictionary
    Set SortDictionaryByKey = dictNew
     
End Function



In practice simply use to return unsorted data:
=Extract_Pairs(C1)

or to sort the data
=Extract_Pairs(M1, "xlAscending")
or
=Extract_Pairs(M1, "xlDescending")

eg:

upload_2018-7-27_13-3-42.png
 
Last edited:
Hi Hui ,

You did not see the second file I uploaded,

For example, in the first number 1234, the solution should be line by line,

12
13
14
23
24
34

David
 
I saw it

I thought you were just listing the pairs, in case a reader didn't understand what you wanted

You didn't mention that that was a new format or layout you wanted ?

My UDF gives exactly what you asked for and then optionally adds the options of sorting the data both Ascending and Descending.
 
If you want an answer to the second format I'd start it as a separate thread/post
 
Or
upload_2018-7-27_14-2-7.png

C8: =MID(Extract_Pairs($C$1),3*ROWS($C$8:C8)-1,2) Ctrl+Shift+Enter
then copy C8 down


Resulting in:

upload_2018-7-27_14-5-48.png
 
Hi Hui ,

That's exactly what I meant,From C8 to C13.

And that's right about combinatronics ,Combinations without repetition

The base is, 6 maximum options ,=COMBIN(4,2) 2 elements cases

The truth is, my understanding of VBA is really small, I can not relate to the code.

David
 
Last edited:
If I have a moment I will write some notes about the code on the weekend

But you can see how powerful the VBA solution is in that the solution now allows for the natural order of results as well as a sorted (increasing or decreasing ) solution

eg:
upload_2018-7-27_16-22-52.png
 
Hi PETER,

The two most important processes, how can easily get to 6 combinations, I did with the function MID ,And the second process, after I did the first, how to do the filter required, I did with the function AGGREGATE .

David
 
upload_2018-7-27_18-14-32.png

CONFESSION!
My workbook contained an error.
It failed when given the number 1212 because I had assumed there would be only a single repeated digit.

Instead of simply accepting the repeating digit pair, in addition to the ordered combinations of unique digits, I need to restrict the pairs generated from second occurrences to the leading diagonal. Hence

diagonal? Limit multiple digit pairs to leading diagonal = ( k = TRANSPOSE(k) )
ordered? Test that the first digit is less than the second = SIGN( digit < TRANSPOSE(digit) )
filter Either a combination of first occurrences or a double digit number
= ( ordered? * first.occurrence? * TRANSPOSE( first.occurrence? ) ) +
( diagonal? * multiple? * TRANSPOSE(multiple?) )

I would observe that this ability to change the logic within the defined names without recourse to the spreadsheet cells make working with the formulae more of a programming activity than an action-led interactive experience.
 
David.

I did consider using
= AGGREGATE(15,6, …, k)
to filter out errors but I eventually settled for the more basic
= SMALL( …, k),
with CSE, to ignore FALSE.

I will be interested to see your solution when the time is right. My main headache was to select functions that operate with arrays rather than references. My initial solution used helper cells and COUNTIFS, which work well as array formulas but only if the starting point is a reference.

Hui
I like the use of dictionary objects :cool:
 
Hi PETER,

I hinted earlier, my solution is completely different, from the link Hui brought,
The idea and logic is very simple, how to simplify a complex formula, a certain order that can help.
You probably did not try to shorten processes.

David
 
In the case where the data is 1234
Are the reverse numbers eg: 41, 42, 43 etc allowable?
 
This version has simplified the logic. I no longer examine the input for duplicates. Instead I treat each digit as if it were distinct and take through 6 pairs through to a list. I then use match to select the first occurrence of any combination.

@Hui I think we are looking for combinations rather than permutations, so 41 duplicates the pair 14.
 

Attachments

I did try to find a shorter solution, but perhaps a simpler one with Power Query. Except for one transformation step, all was done by clicking buttons in the UI.
Full code makes the list in a single cell. Going back 4 steps creates a table with the list values row per row.
Changing or adding 4 digit numbers in the table would keep on working. If the length would need to be variable, additional steps are required to make it dynamic (add some line of codes in the advanced editor). Not sure how to do that for the merging steps though.
Sorting can be added. Duplicate the result column twice and sort it asc, then desc. Like in the UDF this can be managed when creating a function driven by an input parameter.
I suspect there is a smarter way involving some list manipulations over in PQ to make this work in a more robust and elegant way.
Code: 
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Values"),
  #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1", type text}}, "nl-BE"), "Column1", Splitter.SplitTextByRepeatedLengths(1), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
  #"Inserted Merged Column" = Table.AddColumn(#"Split Column by Position", "Merged", each Text.Combine({Text.From([Column1.1], "nl-BE"), Text.From([Column1.2], "nl-BE")}, ""), type text),
  #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Merged.1", each Text.Combine({Text.From([Column1.1], "nl-BE"), Text.From([Column1.3], "nl-BE")}, ""), type text),
  #"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "Merged.2", each Text.Combine({Text.From([Column1.1], "nl-BE"), Text.From([Column1.4], "nl-BE")}, ""), type text),
  #"Inserted Merged Column3" = Table.AddColumn(#"Inserted Merged Column2", "Merged.3", each Text.Combine({Text.From([Column1.2], "nl-BE"), Text.From([Column1.3], "nl-BE")}, ""), type text),
  #"Inserted Merged Column4" = Table.AddColumn(#"Inserted Merged Column3", "Merged.4", each Text.Combine({Text.From([Column1.2], "nl-BE"), Text.From([Column1.4], "nl-BE")}, ""), type text),
  #"Inserted Merged Column5" = Table.AddColumn(#"Inserted Merged Column4", "Merged.5", each Text.Combine({Text.From([Column1.3], "nl-BE"), Text.From([Column1.4], "nl-BE")}, ""), type text),
  #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column5",{"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Values"}, "Attribute", "Value"),
  #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
  #"Removed Duplicates" = Table.Distinct(#"Removed Columns1"),
  #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Values"}, {{"Table", each _, type table}}),
  #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Pairs", each List.Accumulate(Table.Column([Table],"Value"),"",
(state, current) => if state = "" then state&current else state & "," & current)),
  #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Table"})
in
  #"Removed Columns2"
 

Attachments

@GraH - Guido
It is strange that things that are heavy going with formulae are often straightforward with PQ -- and the converse.
PQ wins hands down for sorting and unpivoting but it seems to be a struggle to do the equivalent of
= digit & TRANSPOSE(digit)

My approach to constructing a list of such combinations was to set up a many-to-many relationship starting with a zero-based index column of length n². Integer divide by n and modulo n gave a pair of foreign keys to the digit table but it was not nice!
 
Hi Peter, I blame my lack of knowledge of some M-power. I'm sure there is a smarter, more efficient way of doing it. I haven't found the correct transformation step yet.
I would like to see your approach over in PQ. I get it from what you described, but I can't be bother for the moment do try it myself that way. I tried a very different approach with some list functions, but it resulted in errors.
The challenge is finding a way to keep the separated digits in order and combine them accordingly, without those cumbersome and repeated steps. I like the idea of using the many-to-many relationship to already avoid some of these steps.
 
I would like to see your approach over in PQ.
Click to expand...

The uploaded workbook contains both a formula solution and a PQ solution but with different limitations and characteristics.

The formula approach is valid for any length of digit string from 3 upwards (there is no point in going beyond 18 because triple occurrences change nothing). The PQ solution has been implemented for strings of length 5 but they can include non-numeric characters.

My main problem is that I do not know how to generated n² records out of fresh air. Maybe the answer is to read a sufficiently large dummy table and cut it down to size!
 

Attachments

You must log in or register to reply here.
Back
Top