Register
Log In
Unanswered
Home
June 27, 2016
Offline
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 | |||||
See Answer
Dashboards
June 25, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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
July 3, 2016
Offline
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
Answers Post
Dashboards
June 25, 2016
Offline
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
Offline
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
December 5, 2016
Offline
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.
Dashboards
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: Mynda Treacy
13 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Md. Rahman
Lilian Vo
Jane Kumwenda
David Miller
Efe Onwochei
LynAnn Williams
Brandy Kriger
purna chandar
E K
velissa barr
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
