Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract first line of data group (XL 97)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract first line of data group (XL 97)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •