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.

  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


  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

    Hi haz ,

    Can you upload your formula to a file?

  2. Haz

    Haz Member

    Here you go, working on Excel 2007
    I changed it a little to give unique answers and added the logic behind it on the bottom

    Attached Files:

  3. bines53

    bines53 Active Member

  4. bines53

    bines53 Active Member

    Hello friends ,

    The small challenge (Number with 4 digits) has become a big challenge (without limitation of digits in the cell).

    The main problem is that we have backward or opposite numbers,{25-52,47-74...)

    In this challenge


    My solution was ,MIN(X,Y) = (X > Y +{1,0}=1)* XY

    In the current challenge,

    First step,with this formula


    I come to a pair of numbers without duplicates,

    10 numbers, that the two digits are equal (00,11,22...) and 45 numbers, which are two digits in number, are different ,that is, there are 55 numbers in the game.

    Second step,what combinations are there


    The complete solution,



    Attached Files:

  5. Jan Martens

    Jan Martens New Member

    hi , this is my formula solution.

    It's easy to understand and works for more than 4 digits.


    ascending ordered digits & transpose (ascending ordered digits) gives a square table (array) where the concatenated values we need are above the diagonal

    row()<transpose (row()) is a thruth table of the same dimension as the ordered digits table where the true values are above the diagonal.

    both tables are multiplied.

    large sorts the values in ascending order.
    LARGE(multiplied table;COMBIN(LEN(c1),2)+1-ROW(INDIRECT("1:"&COMBIN(LEN(c1),2))

    duplicates (due to duplicated digits) are not eliminated.
    Last edited: Aug 9, 2018
  6. bines53

    bines53 Active Member

    Hello friends ,

    Thanks to everyone who participated in the challenge, I enjoyed it very much, and I hope there will be more challenges later on.

  7. GraH - Guido

    GraH - Guido Well-Known Member

    Finally @Peter Bartholomew, I have finished my bumpy quest to find the "one stop list function" in Power Query. So to all it might be of interest read along please.

    Big downside, it involves writing a custom function within a function called List.Generate. Not really my strong side, hence the bumpiness of the ride and the code might be tweaked, but it seems to do the job.

    The code for the function looks like this:
    Code (vb):

    let GetPairs = (TxtVar as text) =>

        let StrLen = Text.Length(TxtVar)-1,
            MakePairs =
               List.RemoveNulls (
                List.Distinct (
                 List.Generate( ()=> [i=0, j=0, ListOut=null],
                   each [i] <= StrLen,
                   each if [j] < StrLen-([i]+1)  then
                             [i=[i], j=[j]+1, ListOut= Text.Middle(TxtVar,[i],1) &       Text.Middle(Text.End(TxtVar,StrLen - [i]),[j],1)]
                           else [i=[i]+1, j=0,ListOut= Text.Middle(TxtVar,[i],1) & Text.Middle(Text.End(TxtVar,StrLen - [i]),[j],1)],
                   each [ListOut])))
    in MakePairs

    in GetPairs
    Using this custom function on the listed values in Table1, Column "Values":
    Code (vb):

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}}),
        CallFxGetPairs = Table.AddColumn(#"Changed Type", "GetPairs", each FxGetPairs([Values]))
    Expanding the final generates a table with the pairs.

    The function List.Generate uses 4 arguments, and I did not find the Microsoft help pages all that helpful. But after some googling I found these hits rather informative:

    I'm far from comfortable yet with List.Generate, but I wanted to share anyway.

    Attached Files:

    r2c2 and Peter Bartholomew like this.
  8. Peter Bartholomew

    Peter Bartholomew Active Member


    Attached Files:

  9. r2c2

    r2c2 Active Member

    Interesting problem. I gave up formula driven approach half-way thru once I realized how long and confusing my formula got. So off to PQ. There were some excellent solutions here. I wanted to create a function to generate pairs. To make it generic, I made two functions. one for creating combinations (just pairs really) and another to make the pairs from a given input number.

    Function 1 - getCombinations(inputNum)
    Returns all pairs for an input number N (ie Nc2 Combinations, N*(N-1)/2)

    Code (vb):

        Source = (inputNum as any) => let
            Source = {1..inputNum},
            #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "num1"}}),
            #"Added Custom" = Table.AddColumn(#"Renamed Columns", "num2", each {1..[num1]-1}),
            #"Expanded to Combine" = Table.ExpandListColumn(#"Added Custom", "num2"),
            #"Filtered Rows" = Table.SelectRows(#"Expanded to Combine", each ([num2] <> null))
            #"Filtered Rows"

    Function 2 - make2DigitCombinations(thisNumber)
    This is answer to the challenge. For a given number 1234, this will return all unique two digit combinations.

    Code (vb):

        Source = (thisNumber as any) => let
            Source = thisNumber,
            #"Converted to Table" = #table(1, {{Source}}),
            #"Inserted Text Length" = Table.AddColumn(#"Converted to Table", "Length", each Text.Length(Text.From([Column1], "en-NZ")), type number),
            #"Invoked Custom Function" = Table.AddColumn(#"Inserted Text Length", "combinations", each getCombinations([Length])),
            #"Expanded combinations" = Table.ExpandTableColumn(#"Invoked Custom Function", "combinations", {"num1", "num2"}, {"num1", "num2"}),
            #"Inserted Text Range" = Table.AddColumn(#"Expanded combinations", "Text Range", each Text.Middle(Text.From([Column1], "en-NZ"), [num1]-1, 1) & Text.Middle(Text.From([Column1], "en-NZ"), [num2]-1, 1), type text),
            #"Removed Duplicates" = Table.Distinct(#"Inserted Text Range", {"Text Range"}),
            #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Text Range"}),
            #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Text Range", Int64.Type}}),
            #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text Range", "numbers"}})
            #"Renamed Columns"

    The second function looks verbose, but I haven't used List.Generate or other approaches to zap it. I will investigate this now.
    GraH - Guido likes this.
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Hello r2c2,
    There a couple of things in your solution to learn from, nice.
    It does seems your function returns for "1234" the list 21, 31, 32, 41, 42, 43, where 12, 13, 14, 23, 24, 34 is expected. Easily fixed though, what counts is the approach to build the pattern.
    I also noticed if you don't apply this step below, it keeps working for any variable text string.
    Code (vb):

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Text Range", Int64.Type}}),
    So you might be able to come up with an elegant solution using List.Transform(Many), perhaps needing List.Positions. I've tried, but failed and ended up with that List.Generate(), I actually don't like very much. It should be simpler with PQ. I has held me busy and away from this forum.
    The help seems to suggest you can iterate the list elements easily and apply a transformation on those elements.

    List.TransformMany (list as list, collectionTransform as function, resultTransform as function)
    -> Returns a list whose elements are projected from the input list. The collectionTransform function is applied to each element, and the resultTransform function is invoked to construct the resulting list. The collectionSelector has the signature (x as Any) => ... where x is an element in list. The resultTransform projects the shape of the result and has the signature (x as Any, y as Any) => ... where x is the element in list and y is the element obtained by applying the collectionTransform to that element.
    r2c2 likes this.

Share This Page