Use a formula to organize columns with different amounts of data

Question

When each column is recorded, the values are different sizes:

I would like the values to be pulled to the left so that one column is always well adjusted with the others:

Is there any way to do this using a formula?

link to Spreadsheet:
https://docs.google.com/spreadsheets/d/1iXqdjs_D-NvyES4LMjpzFEY4uDvbdcMZZY5VUvpmbRQ/edit?usp=sharing

It is no quite clear what you mean and how e.g. Jogo: Empoli x Pisa is supposed to get moved from column 4 to column 1. In any case it might be easier with Apps Script than with formula. — ziganotschka

player0 player0 · Accepted Answer · 2020-02-17T12:27:15

values to be pulled to the left

=ARRAYFORMULA(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF(A1:D12<>"", 
 SUBSTITUTE(A1:D12, " ", "♦"), )),,999^99)), " "), "♦", " "))

while equal distribution would be:

={FILTER(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(A1:D12<>"", 
  SUBSTITUTE(A1:D12, " ", "♦"), )),,999^99)),,999^99), " ")), "♦", " "), 
  MOD(ROW(INDIRECT("A1:A"&COUNTA(A1:D12)))-1, 4)=0), 
  FILTER(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(A1:D12<>"", 
  SUBSTITUTE(A1:D12, " ", "♦"), )),,999^99)),,999^99), " ")), "♦", " "), 
  MOD(ROW(INDIRECT("A1:A"&COUNTA(A1:D12)))-2, 4)=0), 
  FILTER(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(A1:D12<>"", 
  SUBSTITUTE(A1:D12, " ", "♦"), )),,999^99)),,999^99), " ")), "♦", " "), 
  MOD(ROW(INDIRECT("A1:A"&COUNTA(A1:D12)))-3, 4)=0), 
  FILTER(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(A1:D12<>"", 
  SUBSTITUTE(A1:D12, " ", "♦"), )),,999^99)),,999^99), " ")), "♦", " "), 
  MOD(ROW(INDIRECT("A1:A"&COUNTA(A1:D12)))-4, 4)=0)}

note that =COUNTA(A1:D12)/4 needs to be a whole number otherwise you will get ARRAY error. to avoid ARRAY error you can use 1 formula per column. paste in A column and drag it till you reach D column:

=FILTER(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF($A$1:$D$12<>"", 
 SUBSTITUTE($A$1:$D$12, " ", "♦"), )),,999^99)),,999^99), " ")), "♦", " "), 
 MOD(ROW(INDIRECT("A1:A"&COUNTA($A$1:$D$12)))-COLUMN(A1), 4)=0)

Use a formula to organize columns with different amounts of data

1 Answers