Results 1 to 3 of 3

Thread: Need help in filtering a big file

  1. #1

    Need help in filtering a big file



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,

    I'm new to this forum and looking for help in filtering a really big excel file.

    I am basically into structural steel designing.
    I have a file with numerous values:
    Each member with some forces (X, Y, Z, etc...) for different load conditions.

    In this group of values, I am looking to filter out rows with only the maximum and minimum values of X, Y, Z....
    The output(result) for first two members is copied in sheet 2, I am looking for a routine to filter out for all the members (sometimes there may be thousands of members in a file)

    Any help in this regard is highly appreciated!!
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,465
    Articles
    0
    Excel Version
    365
    In the attached file there is a macro called blah which when run produces a new sheet. The result is in a different format from yours. Your format produces at last 12 rows of data per Member Number, this one displays the maxima in green and the minima in red, and if they're on the same line the line is not repeated, so fewer lines per Member Number are needed. Also note that the minimum (or maximum) for a given column/Member Number may appear on two lines on your original data; in which case both are output. There's an example of the output in the sheet called Sheet1 (2) (which can be deleted since running the macro will prodce a fresh one exactly the same).

    It looks like your data has been imported from some other kind of output - I was wondering whether it was from a database and the database might be query-able with SQL?

    Anyway, for others, the macro's code:
    Code:
    Sub blah()
    On Error GoTo GoHere
    Application.ScreenUpdating = False
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
    Set NewSheet = ActiveSheet
    With NewSheet
      .Columns("B:C").Delete
      .Rows("1:7").Delete
      .Rows("2:3").Delete
      .Range("U1").Value = "include"
      With Intersect(.UsedRange, .Columns(1))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
        .Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
        lr = NewSheet.UsedRange.Rows.Count
        Range("I2").FormulaArray = "=RC[-6]=MAX(IF(R2C1:R" & lr & "C1=RC1,R2C[-6]:R" & lr & "C[-6]))"
        Range("I2").Copy Range("J2:N2")
        Range("I2:N2").Copy Range("I3:N" & lr)
        Range("O2").FormulaArray = "=RC[-12]=MIN(IF(R2C1:R" & lr & "C1=RC1,R2C[-12]:R" & lr & "C[-12]))"
        Range("O2").Copy Range("P2:T2")
        Range("O2:T2").Copy Range("O3:T" & lr)
        .Offset(1, 20).Resize(.Rows.Count - 1).FormulaR1C1 = "=COUNTIF(RC[-12]:RC[-1],TRUE)"
        .Offset(, 20).AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
        With .Offset(1, 2).Resize(.Rows.Count - 1, 6).SpecialCells(xlCellTypeVisible)
          .Select
          For Each cll In .Cells
            If cll.Offset(, 6).Value = True Then cll.Interior.ColorIndex = 35
            If cll.Offset(, 12).Value = True Then cll.Interior.ColorIndex = 22
          Next cll
        End With
        .Resize(, 8).SpecialCells(xlCellTypeVisible).Copy .Offset(.Rows.Count)
        .AutoFilter
        .EntireRow.Delete
      End With
      .Cells(1).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
      .Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Clear
      .UsedRange.RemoveSubtotal
      .Cells(1).Select
    End With
    GoHere:
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0

    treat excel sheet as a database

    Read support.microsoft.com/kb/257819 on treating a sheet as a database. Setup a query to retrieve what you want then copy all records into an array and dump the array on the New worksheet.

Posting Permissions

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