PDA

View Full Version : Excel help please.



nvnazeer
01-15-2008, 11:08 PM
Gents,
I need a little more help with my Spreadsheet. I have attached the spreadsheet.
The Original spreadsheet is as shown in sheet1. For all items that have same the same "CMN Part No"., I want to automatically add the corresponding values under quantity column and replace this value with the added quantity on the first row and then delete the rows below(having the same "CMN Part No.') for which the quantities are now added to already in the first row.
This needs to be continued whenever the CMN part No. is the same for adjacent rows.
The end result required is shown on Sheet2.
Since my spread sheet is quite big and has several rows which are with unique "CMN part No." and others with same "CMN part No:" repeated. The program should skip the ones which are unique and then addup the quantities where the CMN part No. is same and delete rows whose quantites have been added. Of course the spreadsheet will be sorted by "CMN Part No." so that the rows with the same CMN part No. are together.

Thank you in advance for the help

Nazeer N.V

Bob Phillips
01-16-2008, 03:28 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(i, "C").Value = Application.SumIf(.Columns(1), .Cells(i, "A").Value, .Columns(3))
Else

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

nvnazeer
01-16-2008, 08:44 AM
Thank you Xld . As always you are the ever reliable person in this forum.
Appreciate all the help and support.

Nazeer N.V

nvnazeer
01-16-2008, 01:45 PM
Posted as new post