Hi
Can anyone help me with a formula to extract non blank cells from a row.
I have found a formula which can do this if the data is in a column here:
=IFERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(1:1))),"")
Note : this is an array formula and needs Ctrl Shift Enter
My data is in a row. I thought it would be a simple matter of changing the array size and converting Row to Column - Alas no.
| What I have | What I want | |||||||||
| Red | Orange | Yellow | Blue | Green | Indigo | Violet | ||||
| 4 | 2 | 7 | 4 | 2 | 7 | |||||
Hi Rachael
I'm not an expert on array formulas but somehow I got it working (I hope).
Select a range before entering the modified formula and then pressing Ctrl Shift Enter.
It won't work if you copied the formula across the row.
Sunny
Hi Rachael
Addendum
In the worksheet, cell I3 should read:
Select the range I2:N2 before entering the formula and then pressing CTRL-SHIFT-ENTER
Sunny
Hi All,
if Excel 2010 (or later version) is available, you could expolit AGGREGATE
=IFERROR(INDEX($A2:$G2,AGGREGATE(15,6,COLUMN($A:$G)/ISNUMBER($A2:$G2),COLUMNS($A:A))),"")
Numbers in A2:G2, formula to be copied in I2. Just enter.
Hope it helps
Hi Canapone
The formula doesn't extract text if the range contains a mixture of numbers and text. It only extract the numbers.
I have slightly change the formula to:
=IFERROR(INDEX($A2:$G2,AGGREGATE(15,6,COLUMN($A:$G)/NOT(ISBLANK($A2:$G2)),COLUMNS($A:A))),"")
and it seems to work.
Thanks for sharing.
Sunny
Thanks Sunny and Canapone for your great suggestions. The AGGREGATE function works best for me as I don't need to extract any text.
One thing I need to share is that the syntax COLUMN($A:$G) in =IFERROR(INDEX($A2:$G2,AGGREGATE(15,6,COLUMN($A:$G)/ISNUMBER($A2:$G2),COLUMNS($A:A))),"")
refers to the number of columns (in this instance 7) that the formula is referencing across.
This is important if you want to pick this formula up and reuse it for a different dataset
If you have a row across 7 columns of data, for example, column L - R this syntax ($A:$G) should remain unchanged, because the AGGREGATE function requires to reference the 1,2,3,4,5,6,7 smallest numbers.
If your dataset has 8 columns of data L - S the syntax is ($A:$H) etc
Allow me to suggest a more easy way :
Simply apply sorting : By Rows
Copy paste the require data
Then return the rows to natural order.
Hi David
Rachael did ask for a formula in her post.
"Can anyone help me with a formula to extract non blank cells from a row."
That is why Canapone and I gave the formulas.
Another way without formulas is to select the non-blank cells in a row using the following steps.
1) Press F5 - Goto - Special - Constants.
2) Copy the selected cells.
3) Select target cell and paste as value.
Sunny
