CODEWITHSUNDEEP
excelspreadsheet
DS_London
DS_London

Reputation: 4271

Flattening an array to a single column in Excel

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

Upvotes: 0

Views: 2147

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

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

Upvotes: 2

Related Questions