Macro help
 

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.

Macro help
 

Can you show some sample data - doesn't have to be complete with all columns,
just some Before and After type examples - needs to have at least part #
column, operation number, and any other thing that a decision might need to
be based on.

"Cam" wrote:

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.

Macro help
 

JLatham, Here's the sample:

Befo
Part# operation STA
A 1 John
B 2 Sally
C 6 Sally
A 2 John
A 3 John
C 5 Sally

After:
Part# operation STA
A 3 John
B 2 Sally
C 6 Sally

Deleted or hide row:
A 2 John
A 3 John
C 5 Sally


"JLatham" wrote:

Can you show some sample data - doesn't have to be complete with all columns,
just some Before and After type examples - needs to have at least part #
column, operation number, and any other thing that a decision might need to
be based on.

"Cam" wrote:

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.

Macro help
 

I think the code below will do it for you. Put it into a regular code
module: [Alt]+[F11] to open the VB Editor, Insert | Module from the VBE menu
and copy, paste and edit the code as required. Close VBE and have at it.

The Const values must be changed to match proper sheet name, first row with
data to check, and columns with the 3 items of data in them.

There's also one line of code that will give you ability to decide whether
or not to hide duplicate entries for a max value. Right now it's set to hide
duplicates. by deleting the line of code that says "hideLateDuplicates =
True" duplicates will be left visible. If you leave it in the code, then the
first value with match of highest Op# value will be left visible and all
others will be hidden.

Now, if you'll run this and let me know if it is doing what you need, and
let me know what to do with duplicates, then I'll set it up to actually
delete the rows rather than simply hide them.

Sub MaxOperationPicker()
'change these 5 as required
Const sheetName = "Sheet1"
Const firstDataRow = 2
Const partCol = "A"
Const opCol = "B"
Const nameCol = "C"

Dim lastRow As Long
Dim keyRow As Long
Dim currRow As LoadPictureConstants
Dim currPartNo As String
Dim currName As String
Dim currMax As Long
Dim hideLateDuplicates As Boolean

'make sure we're on the proper sheet
Worksheets(sheetName).Activate
'unhide any hidden rows from previous use
Range(partCol & ":" & partCol).EntireRow.Hidden = False
lastRow = Range(partCol & Rows.Count).End(xlUp).Row
'initialize
keyRow = firstDataRow
Application.ScreenUpdating = False ' speed things up
Do Until keyRow = lastRow
currName = Range(nameCol & keyRow)
currPartNo = Range(partCol & keyRow)
currMax = Range(opCol & keyRow)
currRow = 1 ' use as offset
Do Until ((keyRow + currRow) = lastRow)
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) _
currMax Then
currMax = Range(opCol & keyRow).Offset(currRow, 0)
End If
currRow = currRow + 1
Loop
'we now have max operation for p#/name combination
'go back through and hide all less than max for the combo
currRow = 0 ' reset
hideLateDuplicates = False ' reset
Do Until ((keyRow + currRow) lastRow)
'don't check already hidden rows
If Range(partCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = False Then
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) <= _
currMax Then
'possible match, question is whether or not
'to hide rows with duplicates when the Op #
'matches up.
If Range(opCol & keyRow).Offset(currRow, 0) = _
currMax Then
If hideLateDuplicates Then
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
Else
'leave 1st instance visible always
'delete next command to leave
'duplicates visible
hideLateDuplicates = True
End If
Else ' is less than, hide regardless
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
End If
End If
End If ' hidden row check
currRow = currRow + 1
Loop
keyRow = keyRow + 1
Do While Range(partCol & keyRow). _
EntireRow.Hidden = True And keyRow < lastRow
keyRow = keyRow + 1
Loop
Loop ' keyRow loop
Application.ScreenUpdating = True ' display results
End Sub

"Cam" wrote:

JLatham, Here's the sample:

Befo
Part# operation STA
A 1 John
B 2 Sally
C 6 Sally
A 2 John
A 3 John
C 5 Sally

After:
Part# operation STA
A 3 John
B 2 Sally
C 6 Sally

Deleted or hide row:
A 2 John
A 3 John
C 5 Sally


"JLatham" wrote:

Can you show some sample data - doesn't have to be complete with all columns,
just some Before and After type examples - needs to have at least part #
column, operation number, and any other thing that a decision might need to
be based on.

"Cam" wrote:

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.

Macro help
 

Looks like I may not get back for a day or two, so here's same code with a
routine added at the end that will delete any hidden rows. Same deal as
before, edit as needed and decide whether or not to keep duplicates or not.

Sub MaxOperationPickerWithDelete()
'change these 5 as required
Const sheetName = "Sheet1"
Const firstDataRow = 2
Const partCol = "A"
Const opCol = "B"
Const nameCol = "C"

Dim lastRow As Long
Dim keyRow As Long
Dim currRow As LoadPictureConstants
Dim currPartNo As String
Dim currName As String
Dim currMax As Long
Dim hideLateDuplicates As Boolean

'make sure we're on the proper sheet
Worksheets(sheetName).Activate
'unhide any hidden rows from previous activity
Range(partCol & ":" & partCol).EntireRow.Hidden = False
lastRow = Range(partCol & Rows.Count).End(xlUp).Row
'initialize
keyRow = firstDataRow
Application.ScreenUpdating = False ' speed things up
Do Until keyRow = lastRow
currName = Range(nameCol & keyRow)
currPartNo = Range(partCol & keyRow)
currMax = Range(opCol & keyRow)
currRow = 1 ' use as offset
Do Until ((keyRow + currRow) = lastRow)
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) _
currMax Then
currMax = Range(opCol & keyRow).Offset(currRow, 0)
End If
currRow = currRow + 1
Loop
'we now have max operation for p#/name combination
'go back through and hide all less than max for the combo
currRow = 0 ' reset
hideLateDuplicates = False ' reset
Do Until ((keyRow + currRow) lastRow)
'don't check already hidden rows
If Range(partCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = False Then
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) <= _
currMax Then
'possible match, question is whether or not
'to hide rows with duplicates when the Op #
'matches up.
If Range(opCol & keyRow).Offset(currRow, 0) = _
currMax Then
If hideLateDuplicates Then
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
Else
'leave 1st instance visible always
'delete next command to leave
'duplicates visible
hideLateDuplicates = True
End If
Else ' is less than, hide regardless
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
End If
End If
End If ' hidden row check
currRow = currRow + 1
Loop
keyRow = keyRow + 1
Do While Range(partCol & keyRow). _
EntireRow.Hidden = True And keyRow < lastRow
keyRow = keyRow + 1
Loop
Loop ' keyRow loop

'added for actual delete
'if a row is hidden, delete it
'delete from the bottom up
For keyRow = lastRow To firstDataRow Step -1
If Range(partCol & keyRow).EntireRow.Hidden = True Then
Range(partCol & keyRow).EntireRow.Delete
End If
Next

Application.ScreenUpdating = True ' display results
End Sub


"Cam" wrote:

JLatham, Here's the sample:

Befo
Part# operation STA
A 1 John
B 2 Sally
C 6 Sally
A 2 John
A 3 John
C 5 Sally

After:
Part# operation STA
A 3 John
B 2 Sally
C 6 Sally

Deleted or hide row:
A 2 John
A 3 John
C 5 Sally


"JLatham" wrote:

Can you show some sample data - doesn't have to be complete with all columns,
just some Before and After type examples - needs to have at least part #
column, operation number, and any other thing that a decision might need to
be based on.

"Cam" wrote:

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.