Forum Discussion
Excel 365 - Extracting non-empty values from a row of values?
Hi,
I've been searching and trying out different formulas that I found online and I couldn't get it to work, so I'm a bit perplexed and am asking for some help. It would be amazing if someone smarter than me can show me the way of making this work using formulas, because I have quite a bit of data to go through.
I have tried this formula that I found on reddit after some modification:
1) =IF(COLUMNS($A1:A1)>COUNTA($A1:$Z1),"",INDEX($A1:$Z1,SMALL(IF($A1:$Z1<>"",COLUMN($A1:$Z1)),COLUMNS($A1:A1)))) where the A1:Z1 was replaced with AA:AI but it didn't work.
2) =IFERROR(INDEX($A2:$G2,AGGREGATE(15,6,COLUMN($A:$G)/NOT(ISBLANK($A2:$G2)),COLUMNS($A:A))),"") where A2:G2 was replaced with AA:AI but that also did not work.
I don't really understand both the complex formula above.
What I need to have is this, it would be amazing if someone can show me the way:
| AA | AI | AK | AT | |||||||||||||||||
| WHAT I HAVE | T+0 | T+1 | T+2 | T+3 | T+4 | T+5 | T+6 | T+7 | T+8 | WHAT I WANT | T+0 | T+1 | T+2 | T+3 | T+4 | T+5 | T+6 | T+7 | T+8 | |
| X1 | 177.002 | 177.082 | 175.222 | 175.169 | X1 | 177.002 | 177.082 | 175.222 | 175.169 | |||||||||||
| X2 | 1.632 | 1.632 | 1.632 | 1.632 | X2 | 1.632 | 1.632 | 1.632 | 1.632 |
The cells are not really empty, aren't they? The probably contain a formula.
Change ($AA2:$AI2>0) to ($AA2:$AI2<>"").
4 Replies
Formula in AL2:
=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN($AA2:$AI2)/($AA2:$AI2>0),COLUMNS($AL2:AL2))),"")Copy to the right and down.
- Hi, thanks for your quick response but it still gives me the blanks, which I don't want. I want the order of the extracted value to always be T+0, T+1..,T+3. Any idea how?
The cells are not really empty, aren't they? The probably contain a formula.
Change ($AA2:$AI2>0) to ($AA2:$AI2<>"").
