Take the 2-minute tour ×

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

share|improve this question

marked as duplicate by random Jun 14 '12 at 16:17


this isn't a duplicate question, unless you're reading impaired. –  bharal Jul 4 '13 at 15:47
add comment

2 Answers

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

share|improve this answer
I want eliminate equal words..sorry for confusion..look this pic: img440.imageshack.us/img440/1651/equalwords.png –  user1391247 Jun 14 '12 at 2:48
add comment

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.

share|improve this answer
yes..thank you..the unique words are counted once,,but can u re-write again the formula please..is correctly? i press enter but give me an error..'Name?' –  user1391247 Jun 14 '12 at 3:46
Solved, i replace , into ; thank you –  user1391247 Jun 14 '12 at 8:57
add comment
