Unique values in Excel? [duplicate]

Posted on

QUESTION :

Possible Duplicate:
How can I display only the unique values in one column in Excel?

I want remove equal words, not simply duplicate words, EQUAL words:

enter image description here

ANSWER :

I’m not actually sure I completely understand what exactly you are doing… but here’s a simple answer anyway:

Assuming you will do the following in a separate column e.g Column C.

  1. Write whatever variation of this IF statement that suits you: =IF(B1=LEFT(A1,LEN(A1)),””)
    ==> this function simply returns a null string if a match is found, and the default value if not (you can obviously suppress the defaul value too or process it howwever you want)

  2. Copy and Paste Column C onto itself using Paste Special| Values (only)

  3. Delete Column B

This gives you the luxury of being able to quickly check if output is what you want

Actually, lines are not important in your question.

Copy all cells with relevant data into column A of a new sheet.
Assuming you’ll choose column A, start from A1.

Now type the following function staring at B1:

B1: =CountIf(A:A,A1)

Copy this function down in B, one for each row of data in A.

Only unique values in cells will count 1 in column B, all other B function cells will count more.

Sort columns A:B by B (Ascending), and delete all cells with a value larger than 1.

I hope it helps.

Leave a Reply

Your email address will not be published.