if the sum of 2 numbers equal to another number

[excelNewbie22]

hi!
i need to know if the sum of any 2 numbers in c2:h2 equal to some number within the same range?
for example c2+g2=h2
also, if the sum of any 2 numbers in c3:h3 equal to some number in c2:h2?
for example c3+f3=h2
don't need to know which, just if
if there's mark with 1 if not, with 0
if there's one formula that can save the helper one in i col, the better, if not, also good

test.xlsm
	C	D	E	F	G	H	I
2	1	11	22	25	30	31	12,33,23,47,36,26,55,52,41,31,61,56,53,42,32
3	1	2	4	30	32	33	3,6,5,34,32,31,62,36,34,33,65,63,37,35,34
4	2	5	12	18	26	40	7,17,14,30,23,20,44,38,31,28,66,58,52,45,42
test

Cell Formulas
Range	Formula
I2:I4	I2	=D2+C2&","&E2+D2&","&E2+C2&","&F2+E2&","&F2+D2&","&F2+C2&","&G2+F2&","&G2+E2&","&G2+D2&","&G2+C2&","&H2+G2&","&H2+F2&","&H2+E2&","&H2+D2&","&H2+C2

 

[KRice]

That's an even better idea for rb. That array could be trimmed to eliminate duplicate values for comparison. For example, to compare the sums to the range C2:H3...
rb, UNIQUE(SMALL(C2:H3,SEQUENCE(COUNT(C2:H3))))
Both worked fine in my tests.

I've given some thought to the difference request too, but haven't figured out a good way to do it. I was investigating an approach that would find the first "1" on each row in the "t" matrix and then generate a new matrix that looks like t except that first 1 on each row would be converted to -1. Then all of the sums as they are currently computed would actually be differences. Still, I would think any of the subsequent comparisons would need to be based on the absolute values of the differences.

 

[KRice]

I think to answer the difference question, the formula shown in the green cells will work:

MrExcel_20220728.xlsx
	C	D	E	F	G	H	I	J	K	L
1							same group	different groups	different groups	difference among different rows
2	1	11	22	25	30	31	1	1	1	1
3	1	2	4	30	32	33	1	1	1	1
4	2	5	12	18	26	40	0	1	1	0
Sheet6 (2)

Cell Formulas
Range	Formula
I2:I4	I2	=LET(r,C2:H2,s,SEQUENCE(64,,0),h,DEC2BIN(s,6),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,{1,2,3,4,5,6},1)+0,m,MMULT(t*r,{1;1;1;1;1;1})*(w=2),MAX(--ISNUMBER(MATCH(m,r,0))))
J2:J4	J2	=LET(ra,C2:H2,rb,UNIQUE(SMALL(C1:H2,SEQUENCE(COUNT(C1:H2)))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))
K2:K4	K2	=LET(ra,C2:H2,rb,SORT(UNIQUE(LET(array,C1:H2,rowcount,ROWS(array),colcount,COLUMNS(array),itemcount,rowcount*colcount,MAKEARRAY(itemcount,1,LAMBDA(r,c,INDEX(array,LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),ROUNDUP(r/rowcount,0)) ))))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))
L2:L4	L2	=LET(ra,C2:H2,rb,UNIQUE(SMALL(C1:H2,SEQUENCE(COUNT(C1:H2)))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),tsum,MID(h,SEQUENCE(,c),1)+0,tdiff,IF(LEN(LEFT(h,SEQUENCE(,c)))-LEN(SUBSTITUTE(LEFT(h,SEQUENCE(,c)),"1",""))=1,-1*MID(h,SEQUENCE(,c),1),1*MID(h,SEQUENCE(,c),1)),t,tdiff,m,ABS(MMULT(t*ra,SEQUENCE(c,,,0))*(w=n)),MAX(--ISNUMBER(MATCH(m,rb,0))))

This approach takes Eric's original array called "t" and redefines it as "tsum" because it consists of only 1's and 0's; and subsequent operations will add values corresponding to the 1's. In this new variation, we create a new "tdiff" matrix that converts the first 1 on each row in the original t array to a -1. Then for any rows in tdiff where there are two non-zero numbers, one of them will be -1 and the other will be 1. Subsequent operations will then effectively perform substractions for every combination of values on each row in ra. In order to assign whether the solution should consider the sum or the difference, we then assign the variable "t" to be either "tsum" or "tdiff". In the green cells, I've assigned tdiff to t to take the differences and then look at whether the same difference values are found on the same row or the row above. This formula could be tightened up some since is has some redundant terms that could be pulled out and assigned a variable name in the LET function.

 

[excelNewbie22]

Eric

No, the formula only checks one row of sums with another row of numbers. If you want to compare one row against the 2 previous rows, or the 3 previous rows, or even all previous rows, I think I can do that if you want.

will it check agains every row sepreate or all together?
but now after thinking about it, it can be usefull in any way
so yes please

KRice
thank you for joining in

change the numbers for manually calc and check the formula
it doesn't work for me
no difference in c5:h5 is equal to one of the numbers in c4:h4
also, what's k formula for?

test.xlsx
	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6	same group	different groups	different groups	difference among different rows
2	10	20	30	40	50	60	1	1	0	1
3	1	2	3	4	5	6	1	1	0	1
4	10	20	30	40	50	60	1	1	0	1
5	1	2	3	4	5	6			0	1
test

Cell Formulas
Range	Formula
I2:I4	I2	=LET(r,C2:H2,s,SEQUENCE(64,,0),h,DEC2BIN(s,6),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,{1,2,3,4,5,6},1)+0,m,MMULT(t*r,{1;1;1;1;1;1})*(w=2),MAX(--ISNUMBER(MATCH(m,r,0))))
J2:J4	J2	=LET(ra,C2:H2,rb,UNIQUE(SMALL(C1:H2,SEQUENCE(COUNT(C1:H2)))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))
K2:K5	K2	=LET(ra,C2:H2,rb,SORT(UNIQUE(LET(array,C1:H2,rowcount,ROWS(array),colcount,COLUMNS(array),itemcount,rowcount*colcount,MAKEARRAY(itemcount,1,LAMBDA(r,c,INDEX(array,LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),ROUNDUP(r/rowcount,0)) ))))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))
L2:L5	L2	=LET(ra,C2:H2,rb,UNIQUE(SMALL(C1:H2,SEQUENCE(COUNT(C1:H2)))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),tsum,MID(h,SEQUENCE(,c),1)+0,tdiff,IF(LEN(LEFT(h,SEQUENCE(,c)))-LEN(SUBSTITUTE(LEFT(h,SEQUENCE(,c)),"1",""))=1,-1*MID(h,SEQUENCE(,c),1),1*MID(h,SEQUENCE(,c),1)),t,tdiff,m,ABS(MMULT(t*ra,SEQUENCE(c,,,0))*(w=n)),MAX(--ISNUMBER(MATCH(m,rb,0))))

 

[KRice]

In the example you posted, if we look at the L2 (and down) formula, you have ra set to look at the 6 numbers on the same row and rb is set to look at 12 numbers (6 on the same row and 6 on the row above). You also have t using the tdiff array, so we are looking at differences. If you look at the last row, the formula in L5 takes ra=C5:H5 which consists of values 1,2,3,4,5,6 and therefore differences among all two-way combinations will be 1,2,3,4,5. Since rb is set to C4:H5, among those 12 numbers are the very numbers whose differences are being taken, and we have 1,2,3,4,5 all present in both the original numbers (ra) and in the differences (rb), so a 1 is returned. If you want to compare differences to only the row above, set rb to the range above (e.g. ra=C5:H5 and rb=C4:H4)...and if you want to look at all three rows above, rb=C2:H4; or all four rows above, rb=C:H4.

About your other question, I thought the J and K formulas would be equivalent. I like Eric's (J) better than mine (K), but I'll have to investigate further to understand why they are not producing the same answer. You should be able to perform the sum comparisons using the L formula by assigning tsum to t rather than tdiff.

 

[KRice]

In my tests, the J and K formulas produce the same arrays. They are identical formulas except Eric's method for forming the vertical array of rb values (in the J formula) is much easier to understand and more efficient. I don't see any reason why they would produce different results (as shown in post 13), and when I copy the XL2BB mini-sheet from post 13 into my workbook, the J and K formulas produce the same results. I would recommend ignoring the K formula and use the J formula. The J formula works fine as a generalization of the summed pairs comparisons that can replace the I column formula (just adjust rb to specify the range to use for final matches).

If you want more flexibility out of this approach, then the L formula allows you to specify whether you want to sum pairs or take the difference between pairs of numbers. This is done by assigning either tsum or tdiff to the t variable. This may not work as expected depending on your response to the following discussion:

Will all of your values in C:H be non-negative? I'll preface this explanation by noting that I wrapped the result of the sum operation (that produces the array m in the L formula) with an absolute value function. This was done assuming that C:H values are always non-negative, so summing them still produces non-negative values, and taking the absolute value changes nothing. However, if there are negative values in C:H, the sum of any two values can be either positive or negative, and the ABS() wrapping will lead to erroneous results. When differences are taken between every pair of values on a row in C:H, the position of the negative number relative to the other number involved in the difference equation matters because the tdiff array changes the sign of the leftmost number and then adds it to the rightmost number, thereby taking a difference. But the position of the numbers matters: take -2 and 3 as an example. If they appear in C:H as {c,d,-2,3,g,h} the difference will be -(-2)+3=5...which is a legitimate result and equivalent to 3-(-2)=5. And if they appear in C:H as {c,d,3,-2,g,h} the difference will be -3+(-2)=-5...which is also a legitimate result and equivalent to -2-3=-5. The ABS() function returns 5 for either case, and it is 5 that would be used for matching against any element in the rb array.

A slightly trimmed down version of the L formula is shown here with a stand-alone "rb" variable for declaring the range to use for final matching. The rb range is then converted to a vertical array (rbva) using Eric's method, and rbva is then used when the final matches are performed to determine if any sums/differences are found in the rb range. There are three editing locations in this version of the formula: 2 that are readily found at the beginning of the formula for ra and rb, and the 3rd involves the "t" assignment buried deeper in the formula where either tsum or tdiff would be invoked.

=LET(ra,C2:H2,rb,C1:H2,rbva,UNIQUE(SMALL(rb,SEQUENCE(COUNT(rb)))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),seqc,SEQUENCE(,c),tsum,MID(h,seqc,1)+0,tdiff,IF(LEN(LEFT(h,seqc))-LEN(SUBSTITUTE(LEFT(h,seqc),"1",""))=1,-tsum,tsum),t,tdiff,m,ABS(MMULT(t*ra,SEQUENCE(c,,,0))*(w=n)),MAX(--ISNUMBER(MATCH(m,rbva,0))))

 

[Eric W]

Kirk has taken the formulas beyond where I thought they could go. I couldn't think of a good way to do differences. I decided to go the VBA way and write a UDF (User Defined Function). If you'd like to try it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Function NumberPairMatch(r1 As Range, r2 As Range, mycode As String)
Dim i As Long, j As Long, DicVals As Object, DicComp As Object
Dim d1 As Variant, d2 As Variant, x As Variant, OutArr() As Variant

    Application.Volatile
    Set DicVals = CreateObject("Scripting.Dictionary")
    Set DicComp = CreateObject("Scripting.Dictionary")
   
    d1 = r1.Value
    If UBound(d1) > 1 Then
        NumberPairMatch = "Input range should only be 1 row"
        Exit Function
    End If
   
    For i = 1 To UBound(d1, 2) - 1
        For j = i + 1 To UBound(d1, 2)
            DicVals(IIf(mycode = "+", d1(1, i) + d1(1, j), Abs(d1(1, i) - d1(1, j)))) = 1
        Next j
    Next i
   
    d2 = r2.Value
    For i = 1 To UBound(d2)
        For j = 1 To UBound(d2, 2)
            DicComp(i & ":" & d2(i, j)) = 1
        Next j
    Next i
   
    ReDim OutArr(1 To UBound(d2))
    For i = 1 To UBound(d2)
        OutArr(i) = 0
    Next i
    For Each x In DicVals
        For i = 1 To UBound(d2)
            If DicComp.exists(i & ":" & x) Then OutArr(i) = 1
        Next i
    Next x
   
    NumberPairMatch = Join(OutArr, ",")
       
End Function

Press Alt-Q to close the editor. Now you can use the function as follows:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							Compare with same row, addition	Compare with previous row, addition	Compare with all rows so far, addition	Compare with same row, subtraction	Compare with previous row, subtraction	Compare with all rows so far, subtraction
2	1	11	22	25	30	31	1	0	1	1	0	1
3	1	2	4	30	32	33	1	1	1,1	1	1	1,1
4	2	5	12	18	26	40	0	1	1,1,0	0	0	1,0,0
Sheet4

Cell Formulas
Range	Formula
G2:G4	G2	=numberpairmatch(A2:F2,A2:F2,"+")
H2:H4	H2	=numberpairmatch(A2:F2,A1:F1,"+")
I2:I4	I2	=numberpairmatch(A2:F2,A$2:F2,"+")
J2:J4	J2	=numberpairmatch(A2:F2,A2:F2,"-")
K2:K4	K2	=numberpairmatch(A2:F2,A1:F1,"-")
L2:L4	L2	=numberpairmatch(A2:F2,A$2:$F2,"-")

The first parameter is the row where you want to combine the numbers (the current row in this example), the second parameter is the row(s) you want to search, and the third parameter is a "+" or "-" depending on if you want the sum of any 2 values, or the difference. And as Kirk noted, this only looks for positive differences. As you can see, by changing the parameters you can get any variation that we've discussed so far (unless I missed something). If there are multiple rows being compared, then the result will have multiple values of 0 or 1, one per row.

I could update this to actually show the values that add up to what value too, but I think the output would be messy for the multiple row version.

Hope this is of interest, whether or not you may use it!

Edit:

 

[excelNewbie22]

In the example you posted, if we look at the L2 (and down) formula, you have ra set to look at the 6 numbers on the same row and rb is set to look at 12 numbers (6 on the same row and 6 on the row above). You also have t using the tdiff array, so we are looking at differences. If you look at the last row, the formula in L5 takes ra=C5:H5 which consists of values 1,2,3,4,5,6 and therefore differences among all two-way combinations will be 1,2,3,4,5. Since rb is set to C4:H5, among those 12 numbers are the very numbers whose differences are being taken, and we have 1,2,3,4,5 all present in both the original numbers (ra) and in the differences (rb), so a 1 is returned. If you want to compare differences to only the row above, set rb to the range above (e.g. ra=C5:H5 and rb=C4:H4)...and if you want to look at all three rows above, rb=C2:H4; or all four rows above, rb=C:H4.

understood now, tried changing range, all working good!
thanks very much for the effort and time to explaining

Eric
as usual thanks a lot!

what will be faster? udf or formula? cause also latter on it will be need to apply on very large of rows

 

[excelNewbie22]

while keep checking against few thousands of rows, the udf works slow,
but for the checking data phase
it's good,

thank you all!