Copy text in column 2 from column 1. Repeat text is displayed once only vba

Hi,
I am trying to achieve is as follows:

VBA or not sure of a formula to make list in column 2 of items in column 1 ( however the item will be displayed only once. i.e. repeated items will be listed only once)
How can I achieve it.
Any help please
Kind regards

in cell B2

Please Login or Register  to view this content.

hold CTRL + SHIFT down and press enter while in edit mode of this cell

you can then copy this cell and paste it down 19 rows

Hi ThirtyTwo,
Wow. Great work.
I was trying formula, but was no where close to yours.
I will now study your work
Made my day
Kind regards

glad to help

tbh, I'm not sure how this ARRAY method of creating formula works, i mean - why does pressing CTRL+SHIFT make a difference, and if it just adds {} to the formula, why can't we input those ourselves ?

reason why I could respond so quickly is cos it's a commonly asked question - how to list unique values
solution seems always to be the same, I've yet to come across another method

Google seems to have recognise that it is a common issue and created a solution

In this example you supplied -> https://docs.google.com/spreadsheets...HcymyJ2Uqme0js

Google's solution is that you enter a formula into B2 --- =UNIQUE(A2:A19)
you don't need to put anything in the cells bellow this, there's no copying and pasting of formula
it will create a list of unique values from the range you specified and will update this list as changes are made to the source range

Hi ThirtyTwo,
Apology to re-open this.
I am trying to modify the formula that will check records from A2:A4500 and then make a list in B2 of unique values.
I did check using advanced filter but it is not working.
Any help please
Kind regards

Hi again,
My issue is that from A2 to A4500 I also have value as '0' and I dont want zeros to be counted.
Index formula will also work.

Please Login or Register  to view this content.

Need help
Kind regards