MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with repeating cells


Posted by Joni on June 14, 2001 9:34 AM

I am trying condense large amounts of repeatitive data. I have about 2500 rows of total data. there are sets ranging from 200 to 350 rows where all of the data in one column is the same. If it's possible i would like to be able to delete all of those repeatitive rows. Thanks


Posted by Barrie Davidson on June 14, 2001 10:16 AM

Try advanced filtering

Before you do this you need to make sure your data is sorted. From the main menu select Data|Filter|Advanced Filter. From the pop-box; Specify your list location, select "Copy to another location" (and specify that location in the "Copy To" box), select "Unique records only".

Hope this is what you need.

Regards,
Barrie

Posted by Joni on June 14, 2001 11:17 AM

Re: Try advanced filtering

Thanks for the response. It worked but I would need the entire row of information to with the column that i am sorting. Can that Advance filter do that? I might just be using it wrong. Some one gave me a marco that works but it is very slow and doesn't work all of the time. Maybe you could help me making that better.

Sub DeleteDuplicateRows()

' This macro deletes all rows on the active worksheet that
' have non-unique values in the selected column, including blanks.
'
'

Dim iRow As Long
Dim jRow As Long
Dim LastRow As Long
LastRow = Selection.Rows.Count
If ActiveSheet.UsedRange.Rows.Count < LastRow Then
LastRow = ActiveSheet.UsedRange.Rows.Count
End If
For iRow = LastRow To 1 Step -1
For jRow = 1 To iRow - 1
If Selection.Cells(iRow) = Selection.Cells(jRow) Then Rows(iRow).Delete
Next jRow
Next iRow

End Sub

Thanks again for your help!

Posted by Barrie Davidson on June 14, 2001 12:12 PM

Re: Try advanced filtering

Your question "It worked but I would need the entire row of information to with the column that i am sorting. Can that Advance filter do that?", yes the advanced filter can do that. Just include all the columns you want in your "list location". The only problem you might have would occur if your data contained more than 128 columns (because you have to copy the filtered records to the same sheet, 128+128=256 which is the maximum number of columns).

The macro takes a long time because it is looking at each row used in your spreadsheet.

Hope this helps you out.

Barrie

Posted by Joni on June 14, 2001 1:49 PM

Re: Try advanced filtering

I looked again at what I actually wanted done and it turns out that I need to be able to compare columns "A, C and F" and if they are ALL the same then delete that row. Advanced Filtering isn't doing that. If you or anyone else could help i would be appreciative.

Posted by Barrie Davidson on June 15, 2001 7:46 AM

Instead of advanced filtering

How about changing the macro you originally were using. Try:

Sub DeleteDuplicateRows()

'Written by Barrie Davidson
'
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A2").Select
Do Until Selection.Row > LastRow

If Selection.Value = Selection.Offset(-1, 0).Value And Selection.Offset(0, 2).Value = Selection.Offset(-1, 2).Value And _
Selection.Offset(0, 2).Value = Selection.Offset(-1, 2).Value Then
Selection.EntireRow.Delete
LastRow = LastRow - 1
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("A1").Select

End Sub


Regards,
Barrie

Posted by Joni on June 15, 2001 8:40 AM

Re: Instead of advanced filtering

I'll theive from Barry to improve mine a little: (still only good for positives)

=IF(A1-INT(A1)<=0.95,INT(A1)+0.95,INT(A1)+1.95)

Posted by Barrie Davidson on June 15, 2001 8:51 AM

Joni, I can't read your response below. Please re-post.

The link is to the wrong posting.

Posted by Joni on June 15, 2001 9:21 AM

Re: Joni, I can't read your response below. Please re-post.

Thank you for the macro it is working well except for on little thing. It seems to stop after row 29 or so. I need it to sort about 2000 rows. Your help is really appreciated.
Thanks again,
Joni

Posted by Barrie Davidson on June 15, 2001 9:37 AM

Re: Joni, I can't read your response below. Please re-post.

Joni, the macro does no sorting. Are you pre-sorting your data (on columns A, C, and F) before running the macro - which needs to be done - or do you want the macro to do that for you?

Barrie

Posted by Joni on June 15, 2001 12:53 PM

Re: Joni, I can't read your response below. Please re-post.

. I'm sorry. I ment to say it needs to go through about 2000 rows. Even after I sort the data in A C and F but there are rows (after row 20 or so) which are identical in all three column but they don't seem to be affected. would a sample copy of the data help? Let me know. Thanks again for all of your help.
Joni

Posted by Barrie Davidson on June 15, 2001 1:08 PM

E-mail me a sample but...

I won't be able to look at it today (sorry). I'll have a look and see if I can get the macro to do what you want it to do.

Barrie

Posted by Joni on June 18, 2001 6:37 AM

Re: E-mail me a sample but...

Barrie,
I figured out what was wrong. The macro works great! Thank you very much for all of your help I certainly couldn't have done it with out you.
Thanks again,
Joni