Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove extraneous data or move relevant data

    I have two spreadsheets where I used Perl to extract out data from a storage software solution into Excel. One spreadsheet has 124,000 rows, the other 306,000 rows. Basically it's the exact same set of data repeated for each second on two separate days for a few minutes. What I need to somehow do is extract out all the rows which have the two headers (highlighted in yellow on the attachment) as copied below into two separate sheets (one set for each header) so that I can plot out the results and see what kind of performance I am getting.

    I've tried everything I can think of including some basic macros but because the rows are not consistent, nothing I've tried has worked. Any assistance will be appreciated.


    Thanks.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Seems to me that the text in most of the header fields is unique to that header. So you could use a simple macro (If you're unsure about writing macros record something then if necessary edit the code manually)

    Starting at the top, search for "Read Hit Req/s". Go to the end of the line (end right), select, hold shift, extend selection down to the next blank row (end down), extend selection back to left (end left), copy, paste to new sheet. Repeat for "Total Read Hit Req/s"

    That seems to do it for me. Your OP suggests you want the data transposed as well, but with 300,000 rows you won't be able to do that!

    The code Excel recorded for me (first table only) was

    Sub FirstTable()
    '
    ' FirstTable Macro
    ' Select First table of interest
    '

    '
    Range("A1").Select
    Cells.Find(What:="Read Hit Req/s", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Range("A1").Select
    End Sub

    Ian.

  3. #3
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Ian! I attempted something similar to this and only got the first 'block'. The sample I attached is one very small data set out of what I extracted from the software. I need the macro to loop through the entire spreadsheet instead of just the first data block. Super appreciate the try though!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not sure exactly what you are after. The following code finds each section containing the text "Read Hit Req/s" (I chose that as a "unique indicator", you can choose something different if desired) and copies the "region" (the continuguous range of columns and rows bounded by worksheet edge or blank cols/rows) to a new sheet.

    Steve

    Code:
    Sub Extract()
      Dim wAct As Worksheet
      Dim sFind As String
      Dim wks As Worksheet
      Dim rFound As Range
      Dim s1stAddress As String
      
      sFind = "Read Hit Req/s"
      Set wAct = ActiveSheet
      Set rFound = wAct.UsedRange.Find(sFind)
      If Not rFound Is Nothing Then   ' The value has been found.
        s1stAddress = rFound.Address
        Do
          Set wks = Worksheets.Add
          rFound.CurrentRegion.Copy wks.Range("A1")
          Set rFound = wAct.Cells.FindNext(rFound)
          If rFound.Address = s1stAddress Then Exit Do
        Loop
      End If
    End Sub

  5. #5
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This will work wonderfully. All I really needed was the two data extracts (Individual Reads and Total Reads) extracted out onto two separate sheets/books so that I could plot performance data. I can combine the sheets myself. It was interesting watching the macro in action as sheets went all the to Sheet502!

    Thank you much.

Posting Permissions

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