How to use ARRAYFORMULA and UNIQUE together in Google Sheets

Question

How can I expand the UNIQUE function using ARRAYFORMULA?
Example:
Here is my data set, also can be found here.
I want to see the unique values of each row in next columns.

CL1 | CL2 | CL3 | CL4
JAN | FEB | FEB | JAN >> JAN | FEB
MAR | MAR | JUN | AUG >> MAR | JUN | AUG
NOV | OCT | OCT | OCT >> NOV | OCT
SEP | OCT | JUN | MAR >> SEP | OCT | JUN | MAR

I have used the following formula in cell E2:

=TRANSPOSE(UNIQUE(TRANSPOSE(A2:D2)))

But, the problem is that I need to copy the exact formula in the following rows. While I would prefer to use ARRAYFORMULA.

The problem is that, since ARRAYFORMULA is also using data range, like UNIQUE, there is a conflict. I have tried the following but it did not work:

=ARRAYFORMULA(TRANSPOSE(UNIQUE(TRANSPOSE({A2:A,B2:B,C2:C,D2:D}))))

In other words, I want the exact same result but using ARRAYFORMULA instead of manually entering the formula for each row.

Any other solution would be appreciated.

Answer 1

• not all regular formulas can be converted into array formulas. in this case, you can go only semiautomatic like:

  ={TRANSPOSE(UNIQUE(TRANSPOSE(A2:D2)));
    TRANSPOSE(UNIQUE(TRANSPOSE(A3:D3)));
    TRANSPOSE(UNIQUE(TRANSPOSE(A4:D4)));
    TRANSPOSE(UNIQUE(TRANSPOSE(A5:D5)));
    TRANSPOSE(UNIQUE(TRANSPOSE(A6:D6)))}