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

Share This Page