Covert a table/array to single column, skipping blank cells

[ehesh]

I have a big array of data with 195 columns and 7825 rows. I am trying to convert the array to a single column skipping blank cells.

I found the following formula and have been trying to tweak it with COUNTA(MyData) but so far am struggling to get a positive result

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1).

 

[ehesh]

Power Query should have no trouble doing the conversion.
See 2min video below. Its pretty much.

• Load from range
• Select ALL
• Transform Unpivot Columns
• Close and Load data bac to Excel

You would still need to work out what you are going to do with the data.
If you blow the excel row limit you would either need to return a summarised data set or load it to the Data Model and use it as the basis for your reporting.

Hi Alex, This seems to work. Thanks!

Although I am a bit concerned that I with the formula COUNTA It gave 41726 but with this solution I got 40891. Almost 1k difference. Any advice how I can make sure that I am not missing items?

 

[ehesh]

Yeah, there are indeed a lot of blank cells. I COUNTA the range and confirmed I have 41726 actual items to use, so the total rows would be 41726

Never mind, that was an error on my part while checking the answers. This solved it thanks

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.