Finding Unique Rows Based on Two Columns

Written by Allen Wyatt (last updated October 8, 2022)
This tip applies to Excel 2021 and Excel in Microsoft 365


6

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 2021 and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Viewing More of the Left Margin Area

When working in Draft or Normal view, you may want to view the area just to the left of the document's left margin. ...

Discover More

Attaching Macros to Documents

When you distribute documents to other people, you may want those documents to have associated macros that the reader can ...

Discover More

Finding Cells Filled with a Particular Color

Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Using the ABS Function

Need to find the absolute value of a number? That's where the ABS function comes into play.

Discover More

Using the WEEKNUM Function

Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily ...

Discover More

Returning Blanks or Asterisks from a Lookup

Want to return more than a value when doing a lookup? Here are a couple of ways to do it by adding an IF clause to your ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight minus 8?

2022-10-18 18:19:02

R McNeil

I like that location better!
Thanks!


2022-10-14 11:01:44

Allen

I made a change. Hopefully it helps.

-Allen


2022-10-10 16:35:33

R McNeil

I concur with Michael that the version info would be helpful at the beginning of the tip. I didn't see it at the bottom. Even after reading the comments, I had a hard time finding it. That may be to blame on the browser, but all the more reason to have the version info at the top.

I found the tip interesting, like many of your Word and Excel tips, but it took google-foo to figure out why I couldn't use it since I did not note the version info at the bottom of the tip. I wouldn't have bothered reading the tip if I knew up front that I couldn't use it. It was frustrating enough that I've taken the time to submit a comment if that makes any difference.

In any case, I'll use this as the opportunity to say - Thanks for all the good tips!!


2022-10-09 04:12:31

Michael (Micky) Avidan, "Microsoft®" MVP

Well, Allen,

It might be a good opportunity to make a small change regarding the version issue.

I'm sure that nobody will find it appropriate if the ver. will be mentioned before the content itselfץ

As a matter of fact - it will save a some users time and frustration.

Here is a suggested sample:
(see Figure 1 below)

Figure 1. 


2022-10-08 19:12:35

Allen

I do mention it. At the very end of the tip (as with all my tips) I mention that this one "applies to Microsoft Excel Excel in Microsoft 365 and 2021."

-Allen


2022-10-08 16:35:46

R. A. Williams

You could mention that the UNIQUE function is only available in M$ 365.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.