• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

REMOVING DUPLICATES IN A COLUMN WITHOUT EFFECTING OTHER COLUNMS

J

JAYA KRISHNA PASUMARTY

New Member
I HAVE PROBLEM I WANT TO REMOVE DUPLICATE IN ONE COLUMN THE COLUMN SHOULD BE BLANK ie IF COLUMN A1 HAS JK AND COLUMN A2 HAS JK LET JK IN COLUMN A2 BE REMOVED BUT A2 SHOULD BE BLANK AND REMAIN COLUMNS IE B2 C2 ETC SHOULD BE IN TACT PLZ HELP ME
 
Please stop yelling.


1. For this to work, col A needs to be sorted.

2. Create a helper column. In it, use formula:

=A1=A2

3. Copy formula down

4. Filter helper column on TRUE

5. Select col A, then hit Alt+; to select only visible cells

6. Hit the delete key to clear cells

7. Remove hidden column.

8. DONE
 
Two options spring to mind:


1. a helper column to check for duplicates

2. some VBA code to find and delete the duplicates


1. in an unused column (like cell Z1), enter

=if(countif(B1:C1,A1),"",A1)

Copy this down as far as you need.

Now copy the values from column Z and paste the values into column A.


Hope that helps
 
what Yard has specied is the best way. But in your case the formulae should be


=IF(COUNTIF($A$1:A1,A2),"",A2)


use this in a blank column on row 2 and drag it to the end of your data.
 
THE FIRST OPITION WORKS BUT FALSE CONDITION COME AT THE BOTTOM THAT MEANS FROM BOTTOM UP VIEW MY REQUIEMENT IS SATIDFIED , I EVEN WHAT TOP DOWN VIEW . IAM A BEGINNER AND I REALLLY FEEL EXITED AND IAM HAPPY TO SEE WONDERFUL PEOPLE LIKE U ALL


WITH RESPECT TO SECOND OPITION IAM GETTING ERROR AFTER TYPING THE FORMULA

KINDLY CHECK ONCE ,
 
To clarify, on step 2 of above, that should go in row 2 (so that you get you're top-down view).

Again, please turn off CAPS lock. It's difficult to read.
 
I would like to share this formula if you don't want to sort the data, put this formula in another column =IF(COUNTIF(A$1:A1,A1)=1,A1,"")
 
Use simple below coding...


Sub proTEST()


For i = 1 To Application.WorksheetFunction.CountA(Range("A:A"))


If ActiveCell.Value = ActiveCell.Offset(0, 1).Value Then

ActiveCell.ClearContents

End If


ActiveCell.Offset(1, 0).Select


Next


End Sub


Best Wishes from smkamranqadri


www.sbanalysis.com
 
smkamranqadri,


Check your If statement. I think you have it currently comparing the cells with the cell 1 column across, not 1 row down.
 
There is some ambiguity in OP.

IF COLUMN A1 HAS JK AND COLUMN A2 HAS JK LET JK IN COLUMN A2 BE REMOVED BUT A2 SHOULD BE BLANK AND REMAIN COLUMNS IE B2 C2
Click to expand...

Based on the latter part of this statement, I was assuming that OP really meant to say "If cell A1 has JK and cell A2 has JK let JK in cell A2 be removed but A2 should be blank and cells B2 & C2 should remain." but you may be correct.
 
You must log in or register to reply here.
Back
Top