How to move data to right empty cells in Google Sheets?

user_11001783 0
Viewed 134

I would like to design an organization chart and I am building a supervisor tree like this. 

9  8  7  6  5  4  3  2  1  0
A  B  C                    
D
E  F  B  C

By using vlookup I got the table like above.

Now I would like the table to be like this:

9  8  7  6  5  4  3  2  1  0
                     A  B  C
                           D
                  E  F  B  C

I tried 

=IFS(ISBLANK(I2),H2,ISBLANK(H2),G2,ISBLANK(G2),F2,ISBLANK(F2),E2,ISBLANK(E2),D2,ISBLANK(D2),C2,ISBLANK(C2),B2,ISBLANK(B2),A2)

But it turned out to be #REF

I just would like to move the data to the right, to get rid of those empty cells.

Does anyone know how to do it? Or can we realize it in BigQuery?

google-sheets-query array-formulas google-sheets-formula transpose google-sheets
1 Answers

try:

=ARRAYFORMULA(TRIM(SUBSTITUTE(IFERROR(SPLIT(REPT("♦♠", 
 IF(A2:A<>"", MMULT(IF(A2:J<>"", 0, 1), TRANSPOSE(COLUMN(A2:J)^0)), ))&
 TRANSPOSE(QUERY(TRANSPOSE(IF(A2:J<>"", "♦"&A2:J, )),,999^99)), "♦")), "♠", )))

0

user_56326290
Related