SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1272524%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Extracting%20non-empty%20values%20from%20a%20row%20of%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605021%22%20target%3D%22_blank%22%3E%40AC626%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20in%20AL2%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(2%3A2%2CAGGREGATE(15%2C6%2CCOLUMN(%24AA2%3A%24AI2)%2F(%24AA2%3A%24AI2%26gt%3B0)%2CCOLUMNS(%24AL2%3AAL2)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECopy%20to%20the%20right%20and%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272540%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Extracting%20non-empty%20values%20from%20a%20row%20of%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272540%22%20slang%3D%22en-US%22%3EHi%2C%20thanks%20for%20your%20quick%20response%20but%20it%20still%20gives%20me%20the%20blanks%2C%20which%20I%20don't%20want.%20I%20want%20the%20order%20of%20the%20extracted%20value%20to%20always%20be%20T%2B0%2C%20T%2B1..%2CT%2B3.%20Any%20idea%20how%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272458%22%20slang%3D%22en-US%22%3EExcel%20365%20-%20Extracting%20non-empty%20values%20from%20a%20row%20of%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272458%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20searching%20and%20trying%20out%20different%20formulas%20that%20I%20found%20online%20and%20I%20couldn't%20get%20it%20to%20work%2C%20so%20I'm%20a%20bit%20perplexed%20and%20am%20asking%20for%20some%20help.%20It%20would%20be%20amazing%20if%20someone%20smarter%20than%20me%20can%20show%20me%20the%20way%20of%20making%20this%20work%20using%20formulas%2C%20because%20I%20have%20quite%20a%20bit%20of%20data%20to%20go%20through.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20this%20formula%20that%20I%20found%20on%20reddit%20after%20some%20modification%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%26nbsp%3B%3CSPAN%3E%3DIF(COLUMNS(%24A1%3AA1)%26gt%3BCOUNTA(%24A1%3A%24Z1)%2C%22%22%2CINDEX(%24A1%3A%24Z1%2CSMALL(IF(%24A1%3A%24Z1%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A1%3A%24Z1))%2CCOLUMNS(%24A1%3AA1))))%20where%20the%20A1%3AZ1%20was%20replaced%20with%20AA%3AAI%20but%20it%20didn't%20work.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E2)%26nbsp%3B%3DIFERROR(INDEX(%24A2%3A%24G2%2CAGGREGATE(15%2C6%2CCOLUMN(%24A%3A%24G)%2FNOT(ISBLANK(%24A2%3A%24G2))%2CCOLUMNS(%24A%3AA)))%2C%22%22)%20where%20A2%3AG2%20was%20replaced%20with%20AA%3AAI%20but%20that%20also%20did%20not%20work.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20don't%20really%20understand%20both%20the%20complex%20formula%20above.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20have%20is%20this%2C%20it%20would%20be%20amazing%20if%20someone%20can%20show%20me%20the%20way%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221572px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22145px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3EAA%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3EAI%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145px%22%3E%3CSTRONG%3EAK%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2260px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3EAT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22145px%22%3E%3CSTRONG%3EWHAT%20I%20HAVE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3ET%2B0%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3ET%2B1%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3ET%2B2%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3ET%2B3%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B4%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3ET%2B5%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B6%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B7%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3ET%2B8%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145px%22%3E%3CSTRONG%3EWHAT%20I%20WANT%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B0%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3ET%2B1%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B2%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3ET%2B3%3C%2FTD%3E%3CTD%20width%3D%2260px%22%3ET%2B4%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ET%2B5%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3ET%2B6%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3ET%2B7%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3ET%2B8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22145px%22%3EX1%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E177.002%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E177.082%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E175.222%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E175.169%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145px%22%3EX1%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E177.002%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E177.082%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E175.222%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E175.169%3C%2FTD%3E%3CTD%20width%3D%2260px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22145px%22%3EX2%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145px%22%3EX2%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2277px%22%3E1.632%3C%2FTD%3E%3CTD%20width%3D%2260px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1272458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272572%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Extracting%20non-empty%20values%20from%20a%20row%20of%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605021%22%20target%3D%22_blank%22%3E%40AC626%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cells%20are%20not%20really%20empty%2C%20aren't%20they%3F%20The%20probably%20contain%20a%20formula.%3C%2FP%3E%3CP%3EChange%26nbsp%3B(%24AA2%3A%24AI2%26gt%3B0)%20to%26nbsp%3B(%24AA2%3A%24AI2%26lt%3B%26gt%3B%22%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272594%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Extracting%20non-empty%20values%20from%20a%20row%20of%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272594%22%20slang%3D%22en-US%22%3EYes!!!%20It%20worked!%20Thanks%20a%20lot!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 HAVET+0T+1T+2T+3T+4T+5T+6T+7T+8 WHAT I WANTT+0T+1T+2T+3T+4T+5T+6T+7T+8
X1   177.002177.082175.222175.169   X1177.002177.082175.222175.169     
X2     1.6321.6321.6321.632 X21.6321.6321.6321.632     
4 Replies
Highlighted

@AC626 

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
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

@AC626 

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

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

 

Highlighted
Yes!!! It worked! Thanks a lot!