0

I have two columns both with results of individual unique functions, i.e. both columns contain unrelated dynamic arrays. Is there a way I can combine both these dynamic arrays as a single dynamic 2D array and use in a formula?

Example:

Cell A1 = UNIQUE(rng1) Cell B1 = UNIQUE(rng2)

both always return same number of items.

What I want in cell D1 = SORT(A1#:B1#) --> how do I do this combination?

The notation A1#:B1# gives an error.

dsauce
  • 592
  • 2
  • 14
  • 36

1 Answers1

2

You could use:

=SORT(CHOOSE({1,2},A1#,B1#))
Rory
  • 32,730
  • 5
  • 32
  • 35
  • Perfect, thanks. I like how I can combine UNIQUE, SORT & CHOOSE in these ways to eliminate a significant need to use vba! – dsauce Apr 27 '21 at 14:39
  • How do I do this if A1# or B1# were to have multiple columns? – dsauce Apr 27 '21 at 22:53
  • I can't think of a good dynamic way currently. You could use something like: `=SORT(CHOOSE({1,2,3},A1#,INDEX(B1#,0,1),INDEX(B1#,0,2)))` If you know the columns will be next to each other, you could use: `=SORT(OFFSET(A1#,0,0,,3))` for example – Rory Apr 28 '21 at 07:49
  • Thanks. I see, can devise situational solutions using choose & offset. I'll try and find if there can be a universal solution for stacking/appending arrays in Excel 365. – dsauce Apr 28 '21 at 09:47