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

 

[Fluff]

How about

=LET(a,A2:A7&"",b,B2:F7&"",c,COLUMNS(b),r,c*ROWS(a),s,SEQUENCE(r,,0),x,INDEX(a,INT(s/c)+1),y,INDEX(b,INT(s/c)+1,MOD(s,c)+1),FILTER(CHOOSE({1,2},x,y),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(x,,1)=TRANSPOSE(INDEX(x,,1)))*(y=""),SEQUENCE(r,,,0))<=1))

 

[ehesh]

How about

=LET(a,A2:A7&"",b,B2:F7&"",c,COLUMNS(b),r,c*ROWS(a),s,SEQUENCE(r,,0),x,INDEX(a,INT(s/c)+1),y,INDEX(b,INT(s/c)+1,MOD(s,c)+1),FILTER(CHOOSE({1,2},x,y),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(x,,1)=TRANSPOSE(INDEX(x,,1)))*(y=""),SEQUENCE(r,,,0))<=1))

it works for the short table I had on the picture but when using it on the actual data I get a an error. Is there any modifications I need to make on the formula?

 

[ehesh]

it works for the short table I had on the picture but when using it on the actual data I get a an error. Is there any modifications I need to make on the formula?

I think I got it to work on a smaller range from what I was attempting range but it actually runs out of resource calculating.

 

[Alex Blakenburg]

You might need to rethink what you are doing. 195*7825 = 1,369,375 rows. You have a row limit of 1,048,576.
You would need to have over 300,000 blank cells but perhaps that limit even applies to when it is doing the calculations.

 

[Fluff]

Hadn't paid much attention to the size of data & sequence is limited to 1,048,576 rows so it will not work on that amount of data.
I don't know if power query can do something like this, but that might be a better option if it can do it.

 

[Peter_SSs]

I have no idea how this would go with large data either and it requires the TOCOL function which not all 365 subscribers have yet (it is being rolled out gradually)

22 06 29.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Class1	John	Mary	Sue	Stephan				Class1	John
3	Class2	Clark	Celia						Class1	Mary
4	Class3	Emily	Taylor	Timothy					Class1	Sue
5	Class4	June	Jeff						Class1	Stephan
6	Class5	Mike	Cliff	May	Peter	Jacob			Class2	Clark
7	Class6	Nathan	Sylvia						Class2	Celia
8									Class3	Emily
9									Class3	Taylor
10									Class3	Timothy
11									Class4	June
12									Class4	Jeff
13									Class5	Mike
14									Class5	Cliff
15									Class5	May
16									Class5	Peter
17									Class5	Jacob
18									Class6	Nathan
19									Class6	Sylvia
TOCOL

Cell Formulas
Range	Formula
I2:I19	I2	=TOCOL(IF(B2:F7="",NA(),A2:A7),3)
J2:J19	J2	=TOCOL(B2:F7,1)
Dynamic array formulas.

 

[Alex Blakenburg]

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.

 

[Peter_SSs]

I have no idea how this would go with large data either

Well, I just tried with 200 columns and 7,000 rows with some blank cells scatted throughout. Initially it failed but I think that I must have still had more results than would fit in a column.
Deleted some values and now, still with 7,000 rows and 200 columns but some columns blank, the formulas have produced about 1,045,000 result rows and the recalculation time for the left hand column is about 0.5 seconds and the right column about 0.1 seconds so not too bad!

 

[ehesh]

You might need to rethink what you are doing. 195*7825 = 1,369,375 rows. You have a row limit of 1,048,576.
You would need to have over 300,000 blank cells but perhaps that limit even applies to when it is doing the calculations.

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