2

The formula below returns the least 10 values if including the diagonal values if $C$5="Y". How can we only get non-diagonal values if $C$5="Y".

=VSTACK({"V1","V2","V3","V4"},
    HSTACK({1;2;3;4;5;6;7;8;9;10},TAKE(SORT(--TEXTSPLIT(TEXTAFTER("|"&
    TOCOL(IFS(ISNUMBER(ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-
    IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),0,INDIRECT("'"&$C$9&"'!B2:E5"))))),ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),0,INDIRECT("'"&$C$9&"'!B2:E5"))))&"|"&INDIRECT("'"&$C$9&"'!A2:A5")&"|"&INDIRECT("'"&$C$9&"'!B1:E1")),3),"|",{1,2,3}),"|"),,1),10)))

Sheet 2

1 2 3 4 5
1 83 37 69 80 52
2 89 44 30 64 47
3 56 39 87 88 92
4 60 38 34 35 93
5 21 75 66 47 79

Sheet 3

1 2 3 4 5
1 43 22 46 2 27
2 5 21 37 1 37
3 11 18 6 32 2
4 42 10 10 36 46
5 9 22 1 41 37

Resulting Output:

enter image description here

Desired output:

The first four rows are diagonal values and should not be there and this table should start from row 5.

Improve this question
6
  • 4
    I get this feeling you're not explaining the whole. If we check for the smallest 10, we'd get the 1 first, not 30. Please explain yourself better of what you're trying to accomplish.
    – P.b
    Commented Dec 21, 2023 at 21:37
  • The formula estimates smallest for sheet2-sheet3
    – vp_050
    Commented Dec 22, 2023 at 14:34
  • 3
    The result you show shows something completely different. =LET(r,ROWS(Sheet2!A1:E5),n,NOT(CHOOSEROWS(MUNIT(r),-SEQUENCE(r))+MUNIT(r)),TAKE(SORT(TOCOL(HSTACK(IFS(n,Sheet2!A1:E5),IFS(n,Sheet3!A1:E5)),2)),10)) is the smallest 10 excluding the diagonals.
    – P.b
    Commented Dec 22, 2023 at 14:43
  • @P.b can you suggest a small modification to the formula posted? Also the values are sheet2-sheet3
    – vp_050
    Commented Jan 2 at 16:15
  • 2
    I think the question needs updated then. It doesn't reflect that.
    – P.b
    Commented Jan 3 at 8:46

1 Answer 1

Reset to default
2
+250

Smallest Differences Between Two Matrices

enter image description here

=LET(r_address,"B2:E5",sample,10,s_ref1,C9,s_ref2,C10,yes_no,C5,
    data1,INDIRECT("'"&s_ref1&"'!"&r_address),
    data2,INDIRECT("'"&s_ref2&"'!"&r_address),
    rc,ROWS(data1),
    s,SEQUENCE(rc^2),
    h,"V"&SEQUENCE(,rc),
    rs,ROUNDUP(s/rc,),
    cs,MOD(s-1,rc)+1,
    d,TOCOL(ABS(data1-data2)),
    st,TAKE(SORT(HSTACK(d,rs,cs)),sample),
    f,IF(yes_no="y",FILTER(st,INDEX(st,,2)<>INDEX(st,,3)),st),
VSTACK(h,HSTACK(SEQUENCE(ROWS(f)),f)))

enter image description here

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.