Search code examples
excelduplicatesunique

Returning values of different columns with UNIQUE

So I have returned unique values from another sheet with =UNIQUE('sheet1'!A:A) and am now trying to also return values from one of those rows that are in different columns.

This is the kind of sheet I draw unique vehicles from, the sheet can be changed dynamically to fit new data/changed data, hence why I want to bring the unique values

A vehicle B owner C ID
Bike Justin 3
Bike Justin 3
Bike Justin 3
Car Peter 6
Car Peter 6
Car Peter 6
Car Peter 6
Boat John 12
Boat John 12
Boat John 12
Boat John 12
Boat John 12

From here with UNIQUE IT BECOMES

A vehicle
Bike
Car
Boat

How do I bring information to other columns as the UNIQUE from other columns that are in the same row as the information from the other sheet?

I've tried =IF(A2='sheet1'!A:A, 'sheet1'!B, ""), but it only returns me a #SPILL! I do not want to use the excels own "remove duplicates" option as that is not very dynamic for if the data in the other sheet changes

The end result I am looking for is

A vehicle B owner
Bike 3
Car 6
Boat 12
Solution

  • You can combine the CHOOSE function to create an array with the UNIQUE function

    Enter this formula using CHOOSE to create an array with the two columns.

    =CHOOSE({1,2},A2:A13,C2:C13)
    A vehicle C ID
    Bike 3
    Bike 3
    Bike 3
    Car 6
    Car 6
    Car 6
    Car 6
    Boat 12
    Boat 12
    Boat 12
    Boat 12
    Boat 12

    Then wrap the formula in the UNIQUE function.

    =UNIQUE(CHOOSE({1,2},A2:A13,C2:C13))
    A vehicle C ID
    Bike 3
    Car 6
    Boat 12

    Data and Formula in Excel

    A new function CHOOSECOLS was introduced in September 2022, which can be used to make the formula more readable.

    CHOOSECOLS takes a range/array as an input followed by the columns to be returned.

    =UNIQUE(CHOOSECOLS(A1:C13,1,3))