0

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

ARRAYFORMULA and UNIQUE together in Google Sheets

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.

0
  • 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)))}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.