Google Sheets formula for unique values across columns

Question

I'm familiar with the UNIQUE function in Google Sheets and how it works on rows (see Examples 1 & 2 below).

Is there a function in Google Sheets that operates similarly on columns?

In Example 3 below, UNIQUE behaves as expected by treating this as a single unique row.

I've worked around this by using TRANSPOSE(UNIQUE(TRANSPOSE())) (see Example 4), however would like to know whether there's a built-in solution.

There's not built-in solution AFAIK. Maybe try a Pivot table (but maybe not the best fit) ? or a simple script ? either solution will always be much worse than a simple transpose -> unique -> transpose — pjmg, Commented Sep 3, 2019 at 7:00

Rubén - Volunteer Moderator - · Accepted Answer · 2019-09-18 17:07:39Z

1

Google Sheets doesn't have a "built-in feature" that return unique column values. Your workaround (using TRANSPOSE) is the way to do it.

Reference

answered Sep 18, 2019 at 17:07

46.1k18 gold badges100 silver badges297 bronze badges

Add a comment |

Gary's Student · Accepted Answer · 2020-06-14 19:18:00Z

0

With data in the first row:

=transpose(unique(transpose(A1:J1)))

answered Jun 14, 2020 at 19:18

Gary's Student

1915 bronze badges

Add a comment |

2 Answers 2