Finding Unique Rows Based on Two Columns

Tom has, in Excel, a list of transactions that cover the past year. One column indicates a customer's last name and the next column is the customer's first name. Tom knows he can use the UNIQUE function to return unique rows based on the last name, but he needs the UNIQUE function to take into account both the first and last name. He wonders if there is any way to do this without using a helper column.

Actually, deriving unique values based on multiple columns is easy—all you need to do is to include the columns in the range that you want to use as your source. For instance, let's say that in Tom's case the last name is in column A and the first name in column B. You could, in this case, use the following:

=UNIQUE(A2:B247)

This assumes that your data actually begins in row 2, with the first row being used for column headers, and goes all the way down through row 247. The UNIQUE function, in this case, returns two columns of data because you used two columns in your source parameter. If you don't want two columns returned, then you could modify the source just a bit:

=UNIQUE(A2:A247 & ", " & B2:B247)

This results in a single column being returned because the UNIQUE function uses a single source array that is only a single column wide—a column based on a concatenation of values in two columns.

Note that what is returned will include a comma and space between the last and first names. If you would prefer, you could change the parameter so that it placed the first name first:

=UNIQUE(B2:B247 & " " & A2:A247)

And, you could combine the UNIQUE function with the SORT function to put your list in sorted order. The easiest way to do this is to have the results returned in last name/first name order:

=SORT(UNIQUE(A2:A247 & ", " & B2:B247))

If you would like further examples of how you could return unique values, you may find this webpage helpful:

https://www.exceldemy.com/find-unique-values-from-multiple-columns-in-excel/

Finally, remember that this tip has dealt with just the ability to return unique values from an array. If you also need to pull other information from your original data range, then you'll need to do that using a separate formula, in addition to the UNIQUE-based formula.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5675) applies to Microsoft Excel Excel in Microsoft 365 and 2021.