0

I have a spreadsheet in Excel and I want to list all unique values from an area(A1:C10) in a single column (E). I believe it is supposed to be something like this "{=OFFSET(UNIQUE(A1:C10); 0; 0)}" however it doesn't seem to work.

A   B   C       E
10  5   10      10
20  8   15      20
10  3   30      15
15  5   40      25
25  2   15      30
30  5   20      40
40  3   25      35
20  2   35      5
10  8   40      8
35  1   15      3
                2
                1
uelf1
  • 7
  • 3
  • 4
    `UNIQUE()` is an ms365 function. You tagged Excel-2010. Which one are you actually using? – JvdV Aug 04 '23 at 07:54
  • 1
    @JvdV I did? aah sorry I meant to tag excel-formular. I am of course using ms365 – uelf1 Aug 04 '23 at 09:58

1 Answers1

2

Combine those 3 columns into one with TOCOL then find unique values with UNIQUE:

=UNIQUE(TOCOL(A1:C10,3,TRUE))

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • I don't have the `=ToCol()` function, while my Excel version (Excel.exe, file details) seems to be "16.0.12527.22286", I believe it's some kind of Excel 365 but I'm not sure. What can I do in order to have access to the `=ToCol()` function? – Dominique Aug 04 '23 at 11:17
  • 1
    @Dominique TOCOL is available to these and newer version:s Windows: 2208 (Build 15601) Mac: 16.65 (Build 220911) Web: introduced 15-Sep-2022 iOS: 2.65 (Build 220905) Android: 16.0.15629 . Open blank document, then choose "Account" from bottom left and find "Office updates" -> "Update now" – user11222393 Aug 04 '23 at 11:36