Apr 01 2020 11:19 AM - edited Apr 01 2020 11:48 AM
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 |
Apr 01 2020 11:39 AM
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.
Apr 01 2020 11:46 AM
Apr 01 2020 11:59 AM
SolutionThe cells are not really empty, aren't they? The probably contain a formula.
Change ($AA2:$AI2>0) to ($AA2:$AI2<>"").
Apr 01 2020 12:05 PM
Apr 01 2020 11:59 AM
SolutionThe cells are not really empty, aren't they? The probably contain a formula.
Change ($AA2:$AI2>0) to ($AA2:$AI2<>"").