How to remove blank cells in each row and align the cells left or right?

[Rnkhch]

Hello,

In the attached XL2BB, I have a list of items, and I was wondering if I could write a function and get rid of the blanks in each row and align the cells left or right (as a spilled output), as indicated in the example outputs?

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		Input						Desired output 1					Desired output 2
3		Sucrose	Glycine			Dextran		Sucrose	Glycine	Dextran				Sucrose	Glycine	Dextran
4		Sucrose	Alanine	Albumin				Sucrose	Alanine	Albumin				Sucrose	Alanine	Albumin
5		Sucrose	Proline					Sucrose	Proline						Sucrose	Proline
6		Sucrose		Albumin				Sucrose	Albumin						Sucrose	Albumin
7		Sucrose		Insulin				Sucrose	Insulin						Sucrose	Insulin
8		Sucrose			Tween			Sucrose	Tween						Sucrose	Tween
9		Glucose	Glycine					Glucose	Glycine						Glucose	Glycine
10		Glucose	Alanine					Glucose	Alanine						Glucose	Alanine
11		Glucose	Proline		Tween	Dextran		Glucose	Proline	Tween	Dextran		Glucose	Proline	Tween	Dextran
12		Glucose		Albumin				Glucose	Albumin						Glucose	Albumin
13		Glucose						Glucose								Glucose
14		Glucose			Tween			Glucose	Tween						Glucose	Tween
15		Galactose	Glycine					Galactose	Glycine						Galactose	Glycine
16		Galactose	Alanine					Galactose	Alanine						Galactose	Alanine
17		Galactose	Proline					Galactose	Proline						Galactose	Proline
18		Galactose		Albumin				Galactose	Albumin						Galactose	Albumin
19		Galactose		Insulin				Galactose	Insulin						Galactose	Insulin
20		Galactose			Tween	Dextran		Galactose	Tween	Dextran				Galactose	Tween	Dextran
21
Sheet3

 

[StephenCrump]

I know you said function (which would be pretty straightforward) but I'm sure you'd prefer to make a LAMBDA

I might be overcooking the formula approach, but try:

=LET(Inp,B3:F20,LtoR,1,c,COLUMNS(Inp),t,TOCOL(IF(Inp="","",Inp)),WRAPROWS(SORTBY(t,INT(SEQUENCE(COUNTA(t),,0)/c)+LtoR*(LEN(t)=0)/2),c))

Set LtoR = -1 for the reverse sort.

 

[Peter_SSs]

Like Stephen's, this may not be exactly as your desired outputs as our solutions both contain the same number of columns as the original data (indicated by border below) whereas the borders on your outputs indicate you may only want the minimum columns possible.

22 11 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Input						Desired output 1						Desired output 2
3		Sucrose	Glycine			Dextran		Sucrose	Glycine	Dextran						Sucrose	Glycine	Dextran
4		Sucrose	Alanine	Albumin				Sucrose	Alanine	Albumin						Sucrose	Alanine	Albumin
5		Sucrose	Proline					Sucrose	Proline								Sucrose	Proline
6		Sucrose		Albumin				Sucrose	Albumin								Sucrose	Albumin
7		Sucrose		Insulin				Sucrose	Insulin								Sucrose	Insulin
8		Sucrose			Tween			Sucrose	Tween								Sucrose	Tween
9		Glucose	Glycine					Glucose	Glycine								Glucose	Glycine
10		Glucose	Alanine					Glucose	Alanine								Glucose	Alanine
11		Glucose	Proline		Tween	Dextran		Glucose	Proline	Tween	Dextran				Glucose	Proline	Tween	Dextran
12		Glucose		Albumin				Glucose	Albumin								Glucose	Albumin
13		Glucose						Glucose										Glucose
14		Glucose			Tween			Glucose	Tween								Glucose	Tween
15		Galactose	Glycine					Galactose	Glycine								Galactose	Glycine
16		Galactose	Alanine					Galactose	Alanine								Galactose	Alanine
17		Galactose	Proline					Galactose	Proline								Galactose	Proline
18		Galactose		Albumin				Galactose	Albumin								Galactose	Albumin
19		Galactose		Insulin				Galactose	Insulin								Galactose	Insulin
20		Galactose			Tween	Dextran		Galactose	Tween	Dextran						Galactose	Tween	Dextran
21
Remove Blanks

Cell Formulas
Range	Formula
H3:L20	H3	=TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:F20,LAMBDA(rw,TEXTJOIN(",",1,rw)&REPT(",",COUNTBLANK(rw))))),",",";")
N3:R20	N3	=TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:F20,LAMBDA(rw,REPT(",",COUNTBLANK(rw))&TEXTJOIN(",",1,rw)))),",",";")
Dynamic array formulas.

If you did want the reduced columns ..

22 11 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		Input						Desired output 1						Desired output 2
3		Sucrose	Glycine			Dextran		Sucrose	Glycine	Dextran					Sucrose	Glycine	Dextran
4		Sucrose	Alanine	Albumin				Sucrose	Alanine	Albumin					Sucrose	Alanine	Albumin
5		Sucrose	Proline					Sucrose	Proline							Sucrose	Proline
6		Sucrose		Albumin				Sucrose	Albumin							Sucrose	Albumin
7		Sucrose		Insulin				Sucrose	Insulin							Sucrose	Insulin
8		Sucrose			Tween			Sucrose	Tween							Sucrose	Tween
9		Glucose	Glycine					Glucose	Glycine							Glucose	Glycine
10		Glucose	Alanine					Glucose	Alanine							Glucose	Alanine
11		Glucose	Proline		Tween	Dextran		Glucose	Proline	Tween	Dextran			Glucose	Proline	Tween	Dextran
12		Glucose		Albumin				Glucose	Albumin							Glucose	Albumin
13		Glucose						Glucose									Glucose
14		Glucose			Tween			Glucose	Tween							Glucose	Tween
15		Galactose	Glycine					Galactose	Glycine							Galactose	Glycine
16		Galactose	Alanine					Galactose	Alanine							Galactose	Alanine
17		Galactose	Proline					Galactose	Proline							Galactose	Proline
18		Galactose		Albumin				Galactose	Albumin							Galactose	Albumin
19		Galactose		Insulin				Galactose	Insulin							Galactose	Insulin
20		Galactose			Tween	Dextran		Galactose	Tween	Dextran					Galactose	Tween	Dextran
21
Remove Blanks (2)

Cell Formulas
Range	Formula
H3:K20	H3	=DROP(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:F20,LAMBDA(rw,TEXTJOIN(",",1,rw)&REPT(",",COUNTBLANK(rw))))),",",";"),,-MIN(BYROW(B3:F20,LAMBDA(rw,COUNTBLANK(rw)))))
N3:Q20	N3	=DROP(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:F20,LAMBDA(rw,REPT(",",COUNTBLANK(rw))&TEXTJOIN(",",1,rw)))),",",";"),,MIN(BYROW(B3:F20,LAMBDA(rw,COUNTBLANK(rw)))))
Dynamic array formulas.

 

[StephenCrump]

Ha, thanks Peter!

I started with the same approach, but got stuck, not having noticed that TEXTSPLIT allows two delimiters (row and column).

The new function is even more useful than I thought.

 

[Rnkhch]

Wow , unbelievably fabulous formulas! You guys are both absolute geniuses

And yes, I was thinking of making LAMBDAs (I have a few being cooked and I'll also work on this one too will post them when ready)

And yes, I did want the reduced columns, but having both options is great for when needed

 

[Rnkhch]

I just realized that for extra learning, I'm gonna see if I can figure out aligning at top and bottom as well Then the final LAMBDA can have all four orientation options.