Pairs of numbers

[bines53]

Hi haz ,

Can you upload your formula to a file?

David

 

[Haz]

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

 

[bines53]

Hi Lori,

The identity: MIN(X,Y) = (X + Y - ABS (X - Y))/2

It is more easily in my opinion, according to the formula I gave here in the challenge

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

=SUMPRODUCT(((A1:A10>B1:B10)+{1,0}=1)*A1:B10)

Soon, I will bring up my formula for the complex problem, without limitation of digits in the cell.


David

 

[bines53]

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

 

[Jan Martens]

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.

 

[bines53]

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

 

[GraH - Guido]

I have only ever written one function by following step-by-step guidance so I have nothing to offer at present on that front.
One thing you might consider borrowing from my attempt is the test for multiple occurrences of a digit which you are currently filtering out unless it happens to be the only digit used for the string.

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:

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

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.

 

[Peter Bartholomew]

My technique is lacking in structure but that is a fair assessment of where I am with PQ. Only yesterday did I learn how to generate a sequence of integers as a list. = {1..n}
That was from #9 of Mike Girvin's video series (25 minutes in).

https://www.youtube.com/playlist?list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

 

[r2c2]

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)

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.

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]

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.

#"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.

 

[astrodon]

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

What about uneven list of numbers? EG. {123, 12345, ...}. Or are you just asking for NNNN list?

 

[Peter Bartholomew]

I am continuing my search for problems which are difficult to solve using 365 dynamic arrays.

= LET(
    k,            SEQUENCE(LEN(Input)),
    Explodeλ,     LAMBDA(s, MID(s, k, 1)),
    mask,         k < TRANSPOSE(k),
    v,            SORT(Explodeλ(Input)),
    orderedPairs, IF(mask, v & TRANSPOSE(v), NA()),
    UNIQUE(TOCOL(orderedPairs, 3))
  )

This explodes the string to form a character array then forms ordered pairs. The upper triangle of values are converted to a list and the unique values returned.