#26 — Get Two Numbers from Each of The Two Sets of Numbers and Let Them Equal A Specified Value

Problem description & analysis:

We have two rows of numbers:

Get two numbers from each row (the two numbers can be the same) and make the 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 obtained from row 2 in column C and column D, as shown below:

Solution:

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)

As shown in the picture below:

Explanation:

xjoin function performs 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.

Comments

[Judith-Excel-Sharing]

Want to learn more about the potential of SPL XLL? Here’s the information you may need:
🔺SPL download address: scudata.com/download-Desktop
🔺Plugin Installation Method: c.scudata.com/article/1652061135502
🔺References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
🔺SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

[Judith-Excel-Sharing]

You can also join our communities to discuss more Excel tasks if you are an Excel enthusiast!
📊Discord: discord.gg/hgbKEvJ4
📊Reddit: reddit.com/r/esProc_Desktop/