Identifying duplicates based off two values and deleting rows? 1

[Sbbunno]

Hi

I have been looking around but cannot find what i am looking for.

Column A = Work Order Number
Column B = Operations Number
column F = man hours

I need a macro that can identify the duplicates in column A and B, sum up the man hours into the first line and then delete the duplicate rows.
is this possible?

Thanks for the help

 

[SkipVought]

Hi,

Why?

Why not preserve your source data and use a Pivot Table to aggregate your data? Simple 15 second solution!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sbbunno]

Hi Skip

This data is going to be used for an update into another system and will require the current formatting. i preserved the original data on another sheet.

 

[zelgar]

Since you have everything sorted, the following code should work:

Sub work()
Dim iRow as Long
Dim total as Long

iRow = 3       ' This should be your 2nd line of data

Do Until IsEmpty (Cells(iRow, 1))
   If Cells(iRow - 1, 1) = Cells(iRow, 1) and _
      Cells(iRow - 1, 2) = Cells(iRow, 2) then
      total = Cells(iRow - 1, 6) + Cells(iRow, 6)
      Cells(iRow - 1, 6) = total
      Rows(iRow).Delete
      Else
      iRow = iRow + 1
   End If
Loop
End Sub

 

[SkipVought]

@sbbunno,

I don't see what "formatting" you are referring to. Formatting is simple to do. Aggregating is much more complex. Therefore, a pivot table would solve your dilemma quickly and accurately, in order that your other system can be updated, as "formatting" should mean absolutely nothing to another system.

You did not post a usable example of your sheet that can be copied and pasted into Excel. Since you won't take the time to do that, I'm not going to spend the time to work up a data source that matches what may or not be where your data resides.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sbbunno]

Thanks zelgar that worked perfectly, your one post was very helpfull

Skip I know what I needed for the particular job I was doing, I am new to the forum so apologies if I wasn't sure how to upload a helpfull data sheet.

Regards sbbunno

 

[Andrzejek]

Sbbunno, welcome to the TT

It is customary to show appreciation for the help by clicking on "Great post? Star it" link in the post(s) that was helpful. This way others may see which post(s) was/were the solution to your problem.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.