I have a column that looks something like this:


I want to return the distinct values in a table column like this:


The problem is if i add an 8 to the first column:


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!

  • 1
    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:


In B2 enter the array formula:


and copy down:

enter image description here


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.