Flattening an array to a single column in Excel

Question

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.

stackoverflow.com/questions/62204826/… if your values are unique. — BigBen
– BigBen, Commented Oct 20, 2020 at 14:34

Gary's Student · Accepted Answer · 2020-10-20 14:33:13Z

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.

answered Oct 20, 2020 at 14:33

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DS_London Over a year ago

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.

Gary's Student Over a year ago

@DS_London You are quite welcome....................with VBA you can avoid constants like 4.

DS_London Over a year ago

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.

Collectives™ on Stack Overflow

Flattening an array to a single column in Excel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related