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.