I have a two column spreadsheet. It has thousands of rows. Col A contains a text entry in each row from row 2 onwards (row 1 being the header). I want Col B to contain a formulae which examines the text entry in the adjacent cell in Col A and to return "Yes" or "No" to the following question: Is the text entry in Col A unique having regard to all of the entries above it within Col A.

In other words, taking Cell A1568 as an example, the formula in B1568 would ask the question IF any of the text entries in Cells A2 to A1567 is the same as the text entry in A1568, then return "Yes". If the text entry is not the same as any of the text entries in cell A2 to A1567 then return "No".

(Note: "No" means it is not duplicated, Col B header is "Duplicate").

Thanks in advance

(I know I could pivot table it, but I want a real time "yes"/"no" responsive formulae in Col B)

Hi,

Am Tue, 17 Nov 2015 08:53:27 -0800 (PST) schrieb
:

I have a two column spreadsheet. It has thousands of rows. Col A contains a text entry in each row from row 2 onwards (row 1 being the header). I want Col B to contain a formulae which examines the text entry in the adjacent cell in Col A and to return "Yes" or "No" to the following question: Is the text entry in Col A unique having regard to all of the entries above it within Col A.

In other words, taking Cell A1568 as an example, the formula in B1568 would ask the question IF any of the text entries in Cells A2 to A1567 is the same as the text entry in A1568, then return "Yes". If the text entry is not the same as any of the text entries in cell A2 to A1567 then return "No".

if you want to check all entries for duplicates try in B2:
=IF(COUNTIF(A:A,A2)1,"Duplicate","Unique")
and copy down.

If the first entry should not marked as duplicate try in B2:
=IF(COUNTIF($A$2:A2,A2)1,"Duplicate","Unique")
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, 17 November 2015 17:11:49 UTC, Claus Busch wrote:
Hi,

Am Tue, 17 Nov 2015 08:53:27 -0800 (PST) schrieb
:

I have a two column spreadsheet. It has thousands of rows. Col A contains a text entry in each row from row 2 onwards (row 1 being the header). I want Col B to contain a formulae which examines the text entry in the adjacent cell in Col A and to return "Yes" or "No" to the following question: Is the text entry in Col A unique having regard to all of the entries above it within Col A.

In other words, taking Cell A1568 as an example, the formula in B1568 would ask the question IF any of the text entries in Cells A2 to A1567 is the same as the text entry in A1568, then return "Yes". If the text entry is not the same as any of the text entries in cell A2 to A1567 then return "No".

if you want to check all entries for duplicates try in B2:
=IF(COUNTIF(A:A,A2)1,"Duplicate","Unique")
and copy down.

If the first entry should not marked as duplicate try in B2:
=IF(COUNTIF($A$2:A2,A2)1,"Duplicate","Unique")
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you very much indeed. Gerry