 SOLVED

Highlighted
New Contributor

# 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
4 Replies
Highlighted

# Re: Excel 365 - Extracting non-empty values from a row of values?

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.

Highlighted

# Re: Excel 365 - Extracting non-empty values from a row of values?

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?
Highlighted
Best Response confirmed by AC626 (New Contributor)
Solution

# Re: Excel 365 - Extracting non-empty values from a row of values?

The cells are not really empty, aren't they? The probably contain a formula.

Change (\$AA2:\$AI2>0) to (\$AA2:\$AI2<>"").

Highlighted

# Re: Excel 365 - Extracting non-empty values from a row of values?

Yes!!! It worked! Thanks a lot!