0

I'm trying to gather all of the non empty cells in a n x n range of cells, then output them in a single column that I can drag the fill handle to automatically grab the next valid value. I'm running office 2016 so no FILTER Function. The values need to be collected by top to bottom, and then left to right, so for example:

A1
A2
A3
B1
B2
B3... and so on

See, below for a basic demonstration of what I have and what I need to output.

Demo Data

Improve this question
3
  • You're aware of this requiring a VBA script?
    – Hannu
    Commented May 5 at 8:21
  • Well, I wasn't a 100% certain it would need one. I've been tinkering a fair bit with INDEX, MATCH, ROW, COLUMN and so far can get the correct order for column numbers. But I'm struggling to get the correct order for rows, due to how Excel reads left to right, top to bottom. Once I get the correct row order, it's a simple matter of {=INDEX($B$2:$E$7, row_order_formula, col_order_formula)} to extract the non empty cells in the correct order.
    – Seth
    Commented May 5 at 8:39
  • I suspect this to actually require some simple VBA, creating a list of filled cells while keeping track on which cell data resides in, then sort the list - after which it would be easy place the list content in any sequence of cells. for cell in Selection ...
    – Hannu
    Commented May 5 at 8:43

1 Answer 1

Reset to default
0

I guess I have a solution that requires one Helper Column.

See the below screenshot.

enter image description here

Please note that Cols H & I are not required. They are just kept to give you an idea that you need to generate the correct Row,Col reference for INDEX to fetch the data from the grid of 6x4.

There's one Helper Column K.

The formula in K2 is

=IF(ISBLANK(INDEX($B$2:$E$7,MOD(ROW(A1)-1,6)+1,INT((ROW(1:1)-1)/6)+1)),"",INDEX($B$2:$E$7,MOD(ROW(A1)-1,6)+1,INT((ROW(1:1)-1)/6)+1))

Drag it down. This generates the INDEX function on B2:E7 with correct Row,Col reference and flattens the data into single column.

Now the next step is to generate a list removing Blanks.

The formula in M2 is

=IFERROR(INDEX($K$2:$K$25, SMALL(IF(NOT(($K$2:$K$25)=""), ROW($K$2:$K$25)-ROW($A$1),""), ROW(A1))),"")

Drag it down up to the intended rows.

On my Excel 2021 it does not require to create an Array Formula, but in Excel 2016 both formulas in above example may be required as an Array Formulas for it to work correctly.

You need to press CTRL + SHIFT + ENTERfrom within the formula bar to create an array formula.

I have not extensively tested it. Give it a try. Note -ROW($A$1) in the formula as we are starting in Row 2.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .