Need help in filtering a big file

prashanthmsp

New member
Joined
Aug 29, 2013
Messages
1
Reaction score
0
Points
0
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!!
 

Attachments

  • Member_Forces.xls
    32.5 KB · Views: 13

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,170
Reaction score
12
Points
38
Excel Version(s)
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
 

Attachments

  • excelGuru2082Member_Forces_01.xls
    87 KB · Views: 11

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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.
 
Top