RaqForum 41 No.
109 View  • 
     ...

Get two numbers from each of the two sets of numbers and let them equal to a specified value

 

We have two rows of numbers:

A

B

C

D

E

F

G

1

4211140

4209106

4209974

4211320

4208228

4210873

4211439

2

8859990

8903354

8900184

8901922

8900052

8903899

Get two numbers from each row (the two numbers can be same) and make sum of the four numbers the fixed value 26216692; put the two numbers obtained from row 1 in column A and column B, and the ones got from row 2 in column C and column D, as shown below:

A

B

C

D

4

4208228

4208228

8900184

8900052

Use SPL XLL to get this done:

=spl("=g1=E@1(?1),g2=E@1(?2),E@b(xjoin(g1;g1;g2;g2).select(#1+#2+#3+#4==26216692 && #1>=#2 && #3>=#4))",A1:G1,A2:F2)

Picture1png

xjoin function perfroms cross product. E@1 converts a multilayer sequence to a single-layer one. E@b removes titles, and #1 represents the 1st column of the table.

Source:https://www.reddit.com/r/excel/comments/1cpahhw/how_to_find_which_4_numbers_2_each_from_2_groups/


 
 •  109 View  •