UNIQUE From Non-Adjacent Columns
November 15, 2018 - by Bill Jelen
The other day, I was about to create a unique combination of two non-adjacent columns in Excel. I usually do this with Remove Duplicates or with Advanced Filter, but I thought I would try to do it with the new UNIQUE function coming to Office 365 in 2019. I tried several ideas and none would work. So, I went to the master of Dynamic Arrays, Joe McDaid, for assistance. The answer is pretty cool, and I am sure I will forget it, so I am documenting it for you and for me. I am sure, two years from now, I will Google how to do this and realize "Oh, look! I am the one who wrote the article about this!"
Before getting to the UNIQUE function, take a look at what I am trying to do. I want every unique combination of Sales Rep from column B and Product from column C. Normally, I would follow these steps:
- Copy the headings from B1 and D1 to a blank section of the worksheet
- From B1, choose Data, Filter, Advanced
- In the Advanced Filter dialog, choose Copy To A New Location
- Specify the headings from Step 1 as the Output range
- Choose the box for Unique Values Only
- Click OK
The result is every unique combination of the two fields. Note that the Advanced Filter does not sort the items - they appear in the original sequence.
