writing a formula to return non-empty cells

**pmetzak** · 06-15-2007, 05:54 PM

Hello,

I am interested in writing a formula that will output the data from all non empty cells in a large arrays of cells. Is this possible?

e.g. Cells A20, A21, and A22 contain data. All other cells in column A are empty. I would like to write a formula that can look through A2:A360 and return any data it finds in these cells.

Thank you,

Paul

**Flintstone** · 06-15-2007, 08:03 PM

Hello pmetzak:

Is this what your looking for.

Matt

**Paul** · 06-15-2007, 08:10 PM

I can't see you doing it with one formula, but I do have two options for you:

Option 1: Since your data is in A2:A360, in B2 put this formula: =IF(A2="","",A2) . Then in B3 put this formula and fill down to B360: =IF(AND(B2="",A3=""),"",IF(B2="",A3,IF(AND(B2<>"",A3=""),B2,B2&","&A3)))

Cell B360 will have all non-blank values concatenated, and separated by a comma. Adjust the formula if you don't need a comma to separate entries.

Option 2 is using a macro:

Please Login or Register  to view this content.

This will also join all of your data and separate it with commas, and then put it into cell C1.

**Teethless mama** · 06-17-2007, 01:39 PM

Originally Posted by pmetzak

Hello,

I am interested in writing a formula that will output the data from all non empty cells in a large arrays of cells. Is this possible?

e.g. Cells A20, A21, and A22 contain data. All other cells in column A are empty. I would like to write a formula that can look through A2:A360 and return any data it finds in these cells.

Thank you,

Paul

rng is a define name range from A2:A360

=IF(ISERR(SMALL(IF(rng<>"",ROW(INDIRECT("1:"&ROWS(rng)))),ROWS($1:1))),"",INDEX(rng,SMALL(IF(rng<>"",ROW(INDIRECT("1:"&ROWS(rng)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down