0

I have a m x n array of cells in my spreadsheet, and I want to create a single column of m x n rows. Is there a single spreadsheet function that will do this for me?

I could always create two index columns and use those, but with Excel's more recent embrace of array functionality I was hoping to do it in one function call. I've tried things like =(B3:B6, C3:C6, D3:D6) and other syntaxes with no success. My actual values are unique so I tried UNIQUE(B3:D6) but that treated each column separately, not as an array. I could write a UDF in VBA but that seems like overkill.

enter image description here

DS_London
  • 3,644
  • 1
  • 7
  • 24
  • 1
    https://stackoverflow.com/questions/62204826/excel-unique-across-columns/62205206#62205206 if your values are unique. – BigBen Oct 20 '20 at 14:34

1 Answers1

2

In F3 enter:

=INDEX($B$3:$S$6,MOD(ROWS($1:1)-1,4)+1,ROUNDUP(ROWS($1:1)/4,0))

and copy downward.

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I like this, many thanks! Especially as the "4" could be COUNT($B$3:$B$6) to avoid the hard-coding. I hadn't thought of ROWS($1:1) as a counter before. – DS_London Oct 20 '20 at 15:05
  • @DS_London You are quite welcome....................with VBA you can avoid constants like ***4***. – Gary's Student Oct 20 '20 at 15:08
  • From experience, I think people reach for VBA UDF's too quickly: that's why I wanted to check the hive mind for a built-in function solution. Unless well-written, VBA UDF's don't always handle errors well, can give unexpected results, and also require a .xlsm file which can trigger warnings when opened. – DS_London Oct 20 '20 at 16:01