Returning the distinct/unique values in a column in Excel and make the table expand
You can use the following, in B1 write =A1, in B2 write:
=IFERROR(INDEX($A$1:$A$1000, MATCH(0, COUNTIF($B$1:B1, $A$1:$A$1000), 0)), "")
Array Formula press Ctrl+Shift+Enter at the same time and drag it down
You can drag it more than needed in that case whenever you add Data it will show
Also for $A$1:$A$1000 you can write instead of 1000 more than the maximum rows number
Related videos on Youtube
Jordan
Updated on September 18, 2022Comments
-
Jordan over 1 year
I have a column that looks something like this:
[4,5,6,4,4,4,4,5,6,7]
I want to return the distinct values in a table column like this:
[4,5,6,7]
The problem is if i add an 8 to the first column:
[4,5,6,4,4,4,4,5,6,8]
Then I needed it to return: [4,5,6,7,8]
I need the table to expand. In other words, it needs to update by itself.
I tried using Data --> Advanced. But is does not expand the table and needs to be updated manually.
Any tips?
Thanks for the help!
-
Scott Craner almost 7 yearsPivot tables???
-
Jordan almost 7 yearsI tried that, but I'm not able to sort it my unique number. Im only able to calculate sum, average etc
-
Stephen Rauch almost 7 yearsMaybe with a VBA Script?
-