June 27, 2016
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:
https://answers.microsoft.com/.....c019e28980
=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 |
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
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
June 27, 2016
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
VIP
Trusted Members
June 25, 2016
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
1 Guest(s)