Deleted older rows

A

admiralg

New Member
Joined
Jan 13, 2012
Messages
20
Hi All,
I've been at this solely now for a couple of days and am pulling my hair out. I have a sheet with a couple of thousand rows of info of computer hardware.
I need to delete the old warranty information and leave only the newest or latest info. In the sample sheet attached the computer with serial number 84456 on row 2 and 3 is the same computer, so I need to remove one of these lines (does't matter which one).
Row 4 and 5, again same computer but the warranty detail on line 4 is newer so I need to keep this row and remove the older info (row 5).
Row 6, 7 & 8, again all the same computer but row 7's warranty detail is newer so this needs to stay and row 6 & 8 be deleted.
Row 9, 10 & 11, again all the same computer but row 11 has the more resent warranty information so this row needs to stay and row 9 & 10 be deleted.

I've got over 2000 of these to get through and any help you guys can offer would save me days!!!!

Thanks in advance for even just reading this.

Serial NumberWarranty End
8445631/07/2015
8445631/07/2015
2272323/11/2016
2272323/05/2014
6415710/07/2016
6415723/01/2018
6415710/07/2016
3847423/03/2016
3847423/03/2016
3847405/10/2018

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Bit busy this morning so do not have time to write the whole macro but what I think you want is something that does:

Steps:
1. Get a list of serial numbers
2. Remove Duplicates (Save the remaining to an array)
3. For each value in the array, filter the list and sort by warranty end, delete any rows below the first.
4. Repeat step 3 until sorted.

Let me know if you need help with this.

KR
Chris
 
Upvote 0
ChrisBM said:
Bit busy this morning so do not have time to write the whole macro but what I think you want is something that does:

Steps:
1. Get a list of serial numbers
2. Remove Duplicates (Save the remaining to an array)
3. For each value in the array, filter the list and sort by warranty end, delete any rows below the first.
4. Repeat step 3 until sorted.

Let me know if you need help with this.

KR
Chris
Click to expand...

Hi Chris,
Yeah that pretty much sounds right. Honestly I wouldn't even know where to start with that. I can write basic formulas and amend them but thats pretty much where it ends.
 
Upvote 0
I think this horrible looking formula will do it. It needs to be entered CNTL-SHIFT-ENTER. It should I think place a 1 where the data is not needed and a 0 where it is. You can then filter and delete. Please check on a copy of your workbook first though! Ive assumed your data in A:B with headers so in C2 place:
Code: 
=IF(COUNTIFS(A2:$A$5000,A2,B2:$B$5000,B2)>1,1,IF(MIN(IF($A$2:$A$5000=A2,IF($A$2:$A$5000<>"",$B$2:$B$5000)))=MAX(IF($A$2:$A$5000=A2,IF($A$2:$A$5000<>"",$B$2:$B$5000))),0,IF(MIN(IF($A$2:$A$5000=A2,IF($A$2:$A$5000<>"",$B$2:$B$5000)))=B2,1,0)))
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG09Dec37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nnRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, 1), 0)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
        Q(1) = Q(1) + 1
        [COLOR="Navy"]If[/COLOR] DateValue(Dn.Offset(, 1)) > DateValue(Q(0)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Q(0) = Dn.Offset(, 1)
        [COLOR="Navy"]End[/COLOR] If
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
  [COLOR="Navy"]If[/COLOR] .Item(K)(1) > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = .Item(K)(0)
       [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(K)(0))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Intersect(nRng, Dn.Offset(, 1)) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]If[/COLOR] nnRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nnRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nnRng = Union(nnRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nnRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nnRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Steve,
That looks perfect, but in the image attached you will see it leaves a '0' on row 7 & 8. In theory it should leave a '1' in row '8' as that warranty info is no longer valid.
 
Upvote 0
I get a zero in row 7 and a 1 in row 8. Are you entering CNTL-SHIFT-ENTER? Are you seeing curly brackets {} around your formula in the formula bar?
 
Upvote 0
Yeah im entering CNTL-SHIFT-ENTER but i dont see crly brackets in the formula bar

just to be clear, row 1 is serial number (conscious I have an empty row above in the example)
 
Last edited:
Upvote 0
If you are not seeing the curly brackets you are not entering correctly. Once the formula is in place instead of pressing enter you need to press Control key, then keeping it pressed press the shift key and keeping them both pressed you need to press enter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Ladder League Workbook, replace one row with another
Replies
2
Views
237
CordingBags
C
S
  • Solved
VBA - Delete Duplicate Rows - Except First and Last
Replies
5
Views
273
sksanjeev786
S
airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
779
airforceone
airforceone
D
  • Solved
Filter large amount of data by multiple constraints
Replies
7
Views
195
Peter_SSs
Peter_SSs
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
321
pure vito
P

Forum statistics

Threads
1,214,832
Messages
6,121,845
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top