UNIQUE Formula for Non-Adjacent Columns

Bottom Line: Learn how to create a formula that returns unique combinations of values from non-adjacent columns.

Skill Level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

If you want to follow along, you can use this workbook. It's the same file I use in the video.

Unique List Non-Adjacent Columns.xlsx (23.2 KB)

Pulling Unique Lists from Columns That Aren't Adjacent

Using the unique function to return from nonadjacent columns

We received a great question from Irene, who is an Elevate Excel member. She's curious to know if the UNIQUE function can be used to pull entries from columns that are not side by side or next to each other.

UNIQUE formula for Non-adjacent columns in Excel - Question from Irene

The answer to Irene's question is: on it's own, the UNIQUE function can only find unique combinations from adjacent columns. However, we can write a formula to work around that. This formula will essentially create an array of the non-adjacent columns that you want to reference in the UNIQUE function.

It's a similar process to what I explained in this tutorial on the FILTER Formula to Return Non-Adjacent Columns in Any Order.

Here's how to write the formula we need:

Use INDEX to Create an Array for Non-Adjacent Columns


Below is an example where we want to use the UNIQUE function, which is a dynamic array function, to return a list of unique combinations of Customer Names and Product Names. As you can see, those two columns are not next to each other.

Using the unique function to return from nonadjacent columns

Since the UNIQUE function does not handle that on its own, we have to use use another formula to help. So we're going to use INDEX.

For the INDEX function, the first argument is the array. We will select the entire table for the array.

The next argument is row number (row_num). To define the row numbers we want, we'll first create a list of row numbers using the SEQUENCE function.

The first argument (and the only one we need) in SEQUENCE is rows. To define rows, we will actually use the ROWS function. The only argument for the ROWS function is array, and for that, we will again reference the entire table. This will return every row from the table to our new array.

To define the column number argument (column_num), we can reference column numbers as an array in curly brackets. In our case we want to pull from columns 1 and 3. Those column numbers are specific to the table we're working with, not the sheet overall.

So, all together, our formula to return an array of non-adjacent columns looks like this:

=INDEX(tblUnique,SEQUENCE(ROWS([tblUnique]),{1,3})
The INDEX formula is used to create an array from non-adjacent columns

Wrap the Formula with UNIQUE

Now that we've created a spill range that shows the columns we want, we simply have to wrap our existing formula with the UNIQUE function.

The UNIQUE function has other arguments, but the only one we need to use is array. Our array is defined by the formula we already created. So the final formula looks like this:

=UNIQUE(INDEX(tblUnique,SEQUENCE(ROWS([tblUnique]),{1,3}))

With our duplicates filtered out, our spill range only shows unique entries from the two columns we wanted to pull from.

Only unique entries remain

Conclusion

You can then sort the list of unique values by wrapping the formula in the SORT or SORBY function. Or you could further filter down the results with the FILTER function.

The possibilities are endless in terms of what you can do with dynamic arrays to create flexible, interactive reports. Checkout our video & post on Dynamic Array Formulas & Spill Ranges to learn more.

Please leave a comment below with any questions or suggestions. Until next time!

  • This is really helpful! Could you take it 1 step further and look for counts of each of these unique combinations as well?

    Thanks!

    • Hi Dan,
      Great question! Yes, it is possible and there are several ways to go about it. With formulas you could use a COUNTIFS function.

      =COUNTIFS(tblUnique[Customer Name],F4,tblUnique[Product Name],G4)

      You could also use a pivot table to create the summary report.

      I hope that helps. Thanks again and have a nice day!

  • Thank you Jon for this Video. I have a question. If I want to get the dates sequence , which is only Saturday& Sunday for 3 months. How can I do this?

    • Hi Rahul,
      Great question! This is also possible with dynamic array formulas. Here is a formula that uses SEQUENCE and FILTER. It assumes the start date is in cell M5 and end date in M6.

      =FILTER(SEQUENCE(M6-M5,,M5),WEEKDAY(SEQUENCE(M6-M5,,M5),2)>5)

      We’ll add this formula to our list for future videos. The SEQUENCE function has a lot of uses with dates.

      Thanks!

  • Hi Jon

    Thanks for sharing you can also use the CHOOSE function to join ranges together like this.

    =UNIQUE(CHOOSE({1,2},A2:A6,D2:D6))

  • I can’t find Sequence function in Excel 2019. Is it replaced by some other function in this version of excel?

  • This is very helpful. If i want to sort in descending order, then what should be the formula. Thanks

  • Hi, how can I make the 1 and 4 in this formula below (which are in “{ }” refer to cell inputs? I can only get it to work with “{1,4}” actually typed in the INDEX formula. Ideally I’d like the 1 and 4 to be MATCH() results.

    =INDEX(A1:D10,SEQUENCE(ROWS(A1:D10)),{1,4})

    I’ve tried “{“&1&”,”&4&”}” – with 1 and 4 being cell results, but can’t get it to return the 2 arrays that the typed {1,4} gives.

    I’m using this to create a single array from 2 non-adjacent arrays to be used in LINEST as the independent variables (allowing multiple regression) against a given dependent variable array.

    Thanks

  • Is there a similar way to do it with non-adjacent rows?

    It doesn’t seem to work, as the sequence function has a mandatory row argument, but I need only the column argument. I want the unique headings of two difference tables (which are themselves results of data-based filtering).

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >