Extract first line of data group (XL 97)

Topic

New Reply

The data is extracted from an MS Access database, and includes columns for MachineID and cumulativeHours, among others. Sorted ascending for MachineID and descending for cumulativeHours. Presently several thousand rows, with a variable number of rows per machineID.

I want to filter the data such that no cumulativeHOurs is greater than a user-specified amount (I can do that with AutoFilter), and then extract the first row of data for each machine. For example, the user may specify 10000 hours as the upper limit. I want to find the first occurrence for each machineID that is less than 10000 hours. I can’t specify a lower limit to combine with an AND condition because it will vary depending on the machine. Some machines may have entries that are only 5 hours apart, while others may have entries that are 300 hours apart.

From there, I intend to copy-and-paste into a separate area of the spreadsheet where the data will become “static”.

I am getting stuck trying to find and extract the first, whole row for each machineID. All help appreciated.

Reply | Quote

Replies

Answered my own question after reading “Don’t know what to call it” thread by LonnieB. The answer there inspired me to write this VBA code:

Private Sub cmdExtractMaxHours_Click()
Dim MachineID As Range

Sheets(“copyMax”).Select
Sheets(“CopyMax”).Range(“a2:m1000”).ClearContents
Sheets(1).Select
Range(“CurrentID”).Value = 0

For Each MachineID In Range(“a5:a5000”)
If MachineID.Value = “” Then Exit Sub
If MachineID.Value Range(“CurrentID”).Value Then
If MachineID.Offset(0, 6) <= Range("MaxHours") Then
Range("CurrentID") = MachineID.Value
MachineID.EntireRow.Copy
Sheets("CopyMax").Range("A65536:FA65536").End(xlUp).Offset(1).PasteSpecial Paste:=xlAll
End If
End If
Next
End Sub

Don't know if it's possible to do without VBA, but this works for me.

Reply | Quote