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)
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/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
