# Returning the distinct/unique values in a column in Excel and make the table expand

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!

• Pivot tables??? – Scott Craner Jun 12 '17 at 14:26
• I tried that, but I'm not able to sort it my unique number. Im only able to calculate sum, average etc – Jordan Jun 12 '17 at 14:42
• Maybe with a VBA Script? – Stephen Rauch Jun 12 '17 at 14:53

With data in column A, in B1 enter:

``````=A1
``````

In B2 enter the array formula:

``````=IFERROR(INDEX(\$A\$1:\$A\$1000,INT(SMALL(IF(COUNTIF(B\$1:B1,\$A\$1:\$A\$1000)=0,ROW(\$A\$1:\$A\$1000)+(COLUMN(\$A\$1:\$A\$1000)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(B\$1:B1,\$A\$1:\$A\$1000)=0,ROW(\$A\$1:\$A\$1000)+(COLUMN(\$A\$1:\$A\$1000)*0.01)),1),1)),"")
``````

and copy down: See:

Coderre Formula

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