Tidy up my excel database

[richiwatts]

I have a database that has just over 3000 rows and 3 columns. In the cells on each row are groups of words (one word per cell) e.g

bights bites bytes

The problem is that this group of words shows up 3 times in the database as:

bights bites bytes
bites bytes bights
bytes bights bites

I have this problem for every single line in my database. And they are spread out all over the place as column (A)is in alphabetical order. Really and truelly I should only have about 1000 rows.

Does anyone have any ideas as to how I could tidy this up a get rid of the duplicates? I am a beginner with Excel so I would be grateful if you could explain it in basic terms.

The only way i can think of is to be able to make all off the rows be in alphabetical order so that I get this
bights bites bytes
bights bites bytes
bights bites bytes

That way it would be easier to find all the duplicates and delete them. But how do i do that?

many thanks

 

[Joe Was]

Try, Filter In Place, Unique. Data-Filter from ToolBar or the code below:

Sub myFilter()
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterInPlace, Unique:=True

Range("A1", Range("B65536").End(xlUp)).SpecialCells _
(xlCellTypeVisible).Copy Destination:=Sheets("Sheet1").Range("A1")

ActiveSheet.ShowAllData
End Sub


You will need to change the ranges. This will currently filter con column "A" only. JSW

 

[richiwatts]

thanks Joe

But that looks way above my head

 

[Juan Pablo González]

This macro should sort all the rows in Ascending order.

<pre>Sub SortRows()
Dim lRow As Long
For lRow = 2 To Range("A65536").End(xlUp).Row
Cells(lRow, 1).Resize(1, 3).Sort Key1:=Cells(lRow, 1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
Next lRow
End Sub</pre>

 

[Jim North]

Would it help if the duplicates showed up in a different color?

 

[richiwatts]

I suppose if the duplicates were in a diffent colour I could take the data into word and delete all text with that colour

 

[richiwatts]

Better still Juan macro worked and now the rows are the same. Is there a way to delete duplicate rows?

 

[Jim North]

You don't want to go that route... listen to Joe and Juan.

 

[Juan Pablo González]

On 2002-08-28 10:56, richiwatts wrote:
Better still Juan macro worked and now the rows are the same. Is there a way to delete duplicate rows?

Use Advaced Filter, choosing Unique Entries only.