-1

Say I have:

 ==============
|  a           |
|   a          |
|    a         |
|     a        |
|  b           |
|   b          |
|    b         |
|     b        |
|      b       |
|       b      |
|        b     |
|         b    |
|  c           |
|   c          |
|    c         |
|     c        |
 ==============

As you can see there are "a"x4 and "c"x4 but there are "b"x8. So what I basically want is to remove those extra "b"x4 duplicates so that I get:

 ==============
|  a           |
|   a          |
|    a         |
|     a        |
|  b           |
|   b          |
|    b         |
|     b        |
|  c           |
|   c          |
|    c         |
|     c        |
 ==============

Or How do I remove all duplicates so that I get:

|   a          |
|   b          |
|   c          |

FYI: My excel sheet is huge and the example of "b"x8 could be in random places

  • Can't you just remove those rows? – Leonardo Alves Machado Jul 13 '18 at 18:40
  • @LeonardoAlvesMachado its over 5000 rows in random order, I cant be certain in my eye mistake, see my update for new solution that might be ez – Wajid Jul 13 '18 at 18:42
  • I see... How about using a vba code for that? Have you tried? – Leonardo Alves Machado Jul 13 '18 at 18:44
  • @LeonardoAlvesMachado as I said I could have "d"(s) and in random location, so I cant go to that location and select "d" and then "e" and etc. – Wajid Jul 13 '18 at 18:48
  • @Wajid, check I've posted a solution ! – Rajesh S Jul 14 '18 at 10:48
-1

in the ribbon, Data >>> Remove Duplicates to remove Dups in the column

|improve this answer|||||
  • 1
    in that case it would remove the entire "b", so won't have "b" anymore but I want to keep just 1 – Wajid Jul 13 '18 at 18:56
  • @Wajid, have you tried? it should keep only one "b" (one "a" and so on) – Vrun Jul 13 '18 at 18:59
0

enter image description here

Write this array formula in cell G3, finish with Ctrl+Shift+Enter and fill down.

{=INDIRECT(TEXT(MIN(IF(($A$2:$F$16<>"")*(COUNTIF($G$2:G2,$A$2:$F$16)=0),ROW($2:$16)*100+COLUMN($A:$F),7^8)),"R0C00"),)&""}

You get the list of unique items.

Note, adjust cell references in formula as needed.

|improve this answer|||||
0

Copy all data in a column and use remove duplicates, after removal only unique values will be left in that column.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.