Need help in filtering a big file

prashanthmsp · Aug 29, 2013

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!!

p45cal · Aug 29, 2013

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

WizzardOfOz · Sep 4, 2013

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.

Need help in filtering a big file

prashanthmsp

New member

Attachments

p45cal

Super Moderator

Attachments

WizzardOfOz

New member