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
    Hi haz ,

    Can you upload your formula to a file?

    David
  2. Haz

    Haz Member

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

    Messages:
    705
  4. bines53

    bines53 Active Member

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

    https://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/

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

    In the current challenge,

    First step,with this formula

    =MMULT(--(((MID(ROW($A$1:$A$100)-1,1,1)>MID(TEXT(ROW($A$1:$A$100)-1,"00"),2,1))+{1,0})={1,0}),{1;1})

    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

    =(LEN($C$1)-LEN(SUBSTITUTE(SUBSTITUTE($C$1,MID(TEXT(ROW(A1:A100)-1,"00"),1,1),,1),MID(TEXT(ROW(A1:A100)-1,"00"),2,1),,1)))

    The complete solution,

    =IFERROR(AGGREGATE(15,6,(1/((MMULT(--(((MID(ROW($A$1:$A$100)-1,1,1)>MID(TEXT(ROW($A$1:$A$100)-1,"00"),2,1))+{1,0})={1,0}),{1;1})=2)*((LEN($C$1)-LEN(SUBSTITUTE(SUBSTITUTE($C$1,MID(TEXT(ROW($A$1:$A$100)-1,"00"),1,1),,1),MID(TEXT(ROW($A$1:$A$100)-1,"00"),2,1),,1)))=2)))*(ROW($A$1:$A$100)-1),ROW(AI1)),"")



    David

    Attached Files:

  5. Jan Martens

    Jan Martens New Member

    Messages:
    1
    hi , this is my formula solution.

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

    =LARGE(((SMALL(MID(c1,ROW(INDIRECT("1:"&LEN(c1))),1)+0,ROW(INDIRECT("1:"&LEN(c1))))&TRANSPOSE(SMALL(MID(c1,ROW(INDIRECT("1:"&LEN(c1))),1)+0,ROW(INDIRECT("1:"&LEN(c1))))))+0)*--(ROW((INDIRECT("1:"&LEN(c1))))<TRANSPOSE(ROW((INDIRECT("1:"&LEN(c1)))))),COMBIN(LEN(c1),2)+1-ROW(INDIRECT("1:"&COMBIN(LEN(c1),2))))


    ascending ordered digits & transpose (ascending ordered digits) gives a square table (array) where the concatenated values we need are above the diagonal
    SMALL(MID(c1,ROW(INDIRECT("1:"&LEN(c1))),1)+0,ROW(INDIRECT("1:"&LEN(c1))))&TRANSPOSE(SMALL(MID(c1,ROW(INDIRECT("1:"&LEN(c1))),1)+0,ROW(INDIRECT("1:"&LEN(c1))))))+0)

    row()<transpose (row()) is a thruth table of the same dimension as the ordered digits table where the true values are above the diagonal.
    --(ROW((INDIRECT("1:"&LEN(c1))))<TRANSPOSE(ROW((INDIRECT("1:"&LEN(c1))))))

    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

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


    David
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    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):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}}),
        CallFxGetPairs = Table.AddColumn(#"Changed Type", "GetPairs", each FxGetPairs([Values]))
    in
        CallFxGetPairs
     
    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:
    https://powerpivotpro.com/2016/02/reviewlist-generate-create-tables-thin-air-power-bi-m/
    https://datachant.com/2016/06/03/nested-loop-with-list-generate-in-power-query/
    https://blog.crossjoin.co.uk/2014/0...replacements-of-words-in-text-in-power-query/
    http://www.excelandpowerbi.com/?p=221
    http://excel-inside.pro/blog/2017/06/15/custom-lists-generator-in-power-query-and-power-bi/

    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

    Messages:
    379

    Attached Files:

  9. r2c2

    r2c2 Active Member

    Messages:
    150
    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):

    let
        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))
        in
            #"Filtered Rows"
    in
        Source
     

    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):

    let
        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"}})
        in
            #"Renamed Columns"
    in
        Source

     
    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

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