Want to delete duplicate cells in a row, row by row, going down the sheet.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

HowdeeDoodee

Thread Starter
Joined
Aug 26, 2004
Messages
615
I have rows of data with duplicate cells in each row.

I do not want to delete any rows.

I want to delete only duplicate cells in each row.

There are many ways to delete dups in columns. I do not want to delete rows. I do not want to delete rows if dups appear in the row. I want to delete the dups going across the sheet from left to right, row by row, going down the sheet from top to bottom.

For example, I five rows...

fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep
fox, sheep, goat, fox, dog bone, cat, fox, sheep

I want to delete the duplicate cells in each row. I want to end up with the following...

fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat
fox, sheep, goat, dog bone, cat

Just to clarify, if a cell is repeated in a row, one unique cell should be left in the row in which the repeated cell appears. I only want to delete repeated cells in the rows in which the repeated terms appear, leaving only one unique where the repeated cells appear. If the word "cat" appears five times in row 2 and three times in row 3, after the macro is run, the word "cat" should appear once in row 2 and once in row 3.

In some cells, spaces do appear because in some cells, the cells contain a short phrase (dog bone) rather than just one word. If two or more cells contain a phrase, after running the macro in each row, only one cell should contain the phrase in each row.

Thank you in advance for any replies.
 

HowdeeDoodee

Thread Starter
Joined
Aug 26, 2004
Messages
615
I think I had better revise my example to make sure I get the problem across because the columns are all different.

fox, sheep, goat, fox, dog bone, cat, fox, sheep
sheep, fox, goat, fox, dog bone, cat, fox, sheep
fox, sheep, dog bone, goat, fox, cat, fox, sheep
fox, goat, fox, dog bone, sheep, fox, sheep, cat
cat, fox, sheep, goat, dog bone, fox, sheep

I want to delete the duplicate cells in each row.
I want to end up with the following...

sheep, fox, goat, dog bone, cat
fox, sheep, dog bone, goat, cat
fox, goat, dog bone, sheep, cat
cat, fox, sheep, goat, dog bone
 
Joined
Sep 14, 2005
Messages
6
HowdeeDoodee said:
I think I had better revise my example to make sure I get the problem across because the columns are all different.

fox, sheep, goat, fox, dog bone, cat, fox, sheep
sheep, fox, goat, fox, dog bone, cat, fox, sheep
fox, sheep, dog bone, goat, fox, cat, fox, sheep
fox, goat, fox, dog bone, sheep, fox, sheep, cat
cat, fox, sheep, goat, dog bone, fox, sheep

I want to delete the duplicate cells in each row.
I want to end up with the following...

sheep, fox, goat, dog bone, cat
fox, sheep, dog bone, goat, cat
fox, goat, dog bone, sheep, cat
cat, fox, sheep, goat, dog bone
Please see the attachment for the macro...
Hope it does the trick
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
Or if you prefer a kludge version, something like:

Sub StripRowDupes()
Do Until ActiveCell = ""
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
For Each Cell In Selection
If WorksheetFunction.CountIf(Selection, Cell) > 1 Then
Cell.ClearContents
Else
End If
Next Cell
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
ActiveCell.Range("A2").Select
Loop
End Sub


Prague is v :cool: , BTW.
 

HowdeeDoodee

Thread Starter
Joined
Aug 26, 2004
Messages
615
me_haridas, thank you for the reply.

How do I get to the ThisWorkbook object and when I get to the object, how do I insert this code?

OR

Could you email me an xls file I can download with this object already in the workbook?
 
Joined
Jul 1, 2005
Messages
8,546
HowdeeDoodee said:
Mr. Bomb #21, thank for the code. It ran perfectly on the first tryout. Thanks.
No worries. Thanks for donating to TSG (unless you didn't ;) ). Have a good one.
 
Joined
Sep 14, 2005
Messages
6
HowdeeDoodee said:
me_haridas, thank you for the reply.

How do I get to the ThisWorkbook object and when I get to the object, how do I insert this code?

OR

Could you email me an xls file I can download with this object already in the workbook?
See atached excel doc, and use ALT + F11 to see the ThisWorkBook object !
HTH...
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top