1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Excel Challenges' started by bines53, Jul 26, 2018.

  1. bines53

    bines53 Active Member

    Messages:
    705
    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

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    Why NO UDF?
    This will be relatively simple with a UDF, extremely difficult as a formula ?
  3. bines53

    bines53 Active Member

    Messages:
    705

    Attached Files:

  4. bines53

    bines53 Active Member

    Messages:
    705
    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
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    here is a UDF Solution

    Code (vb):
    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: Jul 27, 2018
    Chirag R Raval and Lori like this.
  6. bines53

    bines53 Active Member

    Messages:
    705
    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
  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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.
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    If you want an answer to the second format I'd start it as a separate thread/post
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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
  10. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
  11. bines53

    bines53 Active Member

    Messages:
    705
    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: Jul 27, 2018
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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
  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    A solution based upon my usual named formula.
    Now I need to study the other solutions for inspiration!

    Attached Files:

  14. bines53

    bines53 Active Member

    Messages:
    705
    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
  15. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    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.
  16. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    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:
  17. bines53

    bines53 Active Member

    Messages:
    705
    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
  18. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    I am all for simplifying a complex formula provided it retains strong links in terms of its business logic.
  19. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    In the case where the data is 1234
    Are the reverse numbers eg: 41, 42, 43 etc allowable?
  20. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    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.

    Attached Files:

  21. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    Just realised, the same formula works for an arbitrary number of digits. All that was needed was to reformulate the counters.

    Attached Files:

  22. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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 (vb):

    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"
     

    Attached Files:

  23. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    @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!
  24. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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.
    Chirag R Raval likes this.
  25. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    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!

    Attached Files:

Share This Page