MartyCollins
New Member
- Joined
- Jan 21, 2022
- Messages
- 38
- Office Version
- 365
- Platform
- Windows
Hi there,
So i have a couple of spilled arrays side by side that have been working fine for the last 2 years or so.
So to take advantage of Hstack i am trying to put them all into one formula.
They all stack together fine except the final column which is a spilled rank of the adjacent column.
Its sensitive data so here is a really simple Example to exlpain:
On the left.. 2 spilled arrays, on the right Hstack brininging the 2 arrays together (My real data has several complex arrays and they stack together fine..except for the rank!)
Thanks,
Martin.
So i have a couple of spilled arrays side by side that have been working fine for the last 2 years or so.
So to take advantage of Hstack i am trying to put them all into one formula.
They all stack together fine except the final column which is a spilled rank of the adjacent column.
Its sensitive data so here is a really simple Example to exlpain:
On the left.. 2 spilled arrays, on the right Hstack brininging the 2 arrays together (My real data has several complex arrays and they stack together fine..except for the rank!)
Thanks,
Martin.
Lamdbas.xlsx | |||||||
---|---|---|---|---|---|---|---|
R | S | T | U | V | |||
4 | Individual Spilled Arrays | Hstack | |||||
5 | Random | Rank | Random | Rank | |||
6 | 41 | 11 | 94 | #VALUE! | |||
7 | 1 | 1 | 61 | #VALUE! | |||
8 | 49 | 13 | 5 | #VALUE! | |||
9 | 89 | 19 | 43 | #VALUE! | |||
10 | 86 | 18 | 64 | #VALUE! | |||
11 | 68 | 15 | 63 | #VALUE! | |||
12 | 10 | 5 | 3 | #VALUE! | |||
13 | 38 | 10 | 45 | #VALUE! | |||
14 | 28 | 7 | 30 | #VALUE! | |||
15 | 6 | 3 | 72 | #VALUE! | |||
16 | 7 | 4 | 39 | #VALUE! | |||
17 | 31 | 9 | 45 | #VALUE! | |||
18 | 30 | 8 | 71 | #VALUE! | |||
19 | 73 | 16 | 1 | #VALUE! | |||
20 | 5 | 2 | 68 | #VALUE! | |||
21 | 46 | 12 | 20 | #VALUE! | |||
22 | 90 | 20 | 44 | #VALUE! | |||
23 | 49 | 13 | 85 | #VALUE! | |||
24 | 25 | 6 | 79 | #VALUE! | |||
25 | 77 | 17 | 17 | #VALUE! | |||
Goodly_matchTax |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R6:R25 | R6 | =RANDARRAY(20,1,1,100,TRUE) |
S6:S25 | S6 | =RANK.EQ(R6#,R6#,1) |
U6:V25 | U6 | =LET( Randoms, RANDARRAY(20,1,1,100,TRUE), Priority, RANK.EQ(Randoms,Randoms,1), Hstack(Randoms,Priority) ) |
Dynamic array formulas. |