Ignore duplicates in list

[cmb80]

Hi All, I want to return only unique values from a list. The list may have some blank cells and I have given an example below. The below cells are A1:A10. I would like the unique numbers shown in B1- Bx (as shown in the 2nd list below) with a formula if possible.


Col A Col B
116944311 116944311
116944048 116944048
116943904 116943904
116943904 116945678


116945678

[bmouse]

Copy the entire list from Col A to Col B, then use Remove Duplicates under the Data tab.

[Richard Buttrey]

Hi,

Why not just copy the column A values underneath the last column B value then just use the DATA Remove Duplicates menu option on column B.

[cmb80]

Hi all, I want to use a formula to do this if possible.

[Richard Buttrey]

Hi all, I want to use a formula to do this if possible.

Why? What is it about the remove duplicates you don't like?

[cmb80]

The data is updating constantly in a hidden sheet. If I use "remove duplicates" I have to manually do this every minute (when the data updates) or write a macro to do this every minute also.

[Fotis1991]

ARRAY formula.

Please Login or Register  to view this content.

In B2 and copy down.



-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[cmb80]

ARRAY formula.

Please Login or Register  to view this content.

In B2 and copy down.



-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Thank you, thats what I was looking for - don;t want to push my luck but is the same result possible without the Array?

[Fotis1991]

Assuming that we work with numbers in columnA, then in B2 and copy down try this.

Please Login or Register  to view this content.

[cmb80]

A great help, many thanks