Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a very large worksheet with hundreds of rows of data. In column A, some of the cells begin with the letters "SR Contract:" and are followed (in the same cell) by 6 numbers, and then varuious words. What I would like to do is "extrapolate" (if that is the right word) just the 6 numbers that appear in such rows to column A on a new worksheet. I would like for the numbers to be filled in in each row one after the other on the new worksheet, regardless of how they appear on the original worksheet (i. e., there might be one or more rows on the original worksheet in between the rows that have the data I am looking for, but on the new worksheet, I want the numbers to appear in rows 1,2,3,4,5,etc, in column A). Any ideas? Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a small sample workbook with some dummy data that are representative of the real data? That would make it easier to help you. Thanks in advance.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794928' date='25-Sep-2009 14:17']Could you post a small sample workbook with some dummy data that are representative of the real data? That would make it easier to help you. Thanks in advance.[/quote]


    Yes, I will post two worksheets-the first showing the original data, and the second showing how I would like it on the new worksheet.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794928' date='25-Sep-2009 14:17']Could you post a small sample workbook with some dummy data that are representative of the real data? That would make it easier to help you. Thanks in advance.[/quote]

    All in one, Sheets 1 and 2
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this macro:

    Code:
    Sub ExtractNumbers()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      ' Modify the names as needed
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet3")
      m = wshSrc.Cells(wshSrc.Rows.Count, 1).End(xlUp).Row
      For s = 1 To m
    	If wshSrc.Cells(s, 1) Like "SR Contract*" Then
    	  t = t + 1
    	  wshTrg.Cells(t, 1) = Val(Mid(wshSrc.Cells(s, 1), 14))
    	End If
      Next s
    End Sub

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794938' date='25-Sep-2009 14:48']Try this macro:

    Code:
    Sub ExtractNumbers()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      ' Modify the names as needed
      Set wshSrc = Worksheets("Sheet1")
    
    Thanks Hans.  (Actually one little tweak:  Sheet3 should be Sheet2.)
      Set wshTrg = Worksheets("Sheet3")
      m = wshSrc.Cells(wshSrc.Rows.Count, 1).End(xlUp).Row
      For s = 1 To m
    	If wshSrc.Cells(s, 1) Like "SR Contract*" Then
    	  t = t + 1
    	  wshTrg.Cells(t, 1) = Val(Mid(wshSrc.Cells(s, 1), 14))
    	End If
      Next s
    End Sub
    [/quote]

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Could you use this formula in cell A2 of shhet2 and copy down:

    =IF(ISERROR(MID(Sheet1!A1,FIND(": ",Sheet1!A1)+3,6)),"",MID(Sheet1!A1,FIND(": ",Sheet1!A1)+3,6))

    and then use an Autofilter to show non Blanks?
    Jerry

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Great work Hans-works like a charm. A little nuance addition: please see attached revised file. Need to include 2 total numbers. What would I need to do to the code to include these as well?
    Thanks in advance.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here you go (next time, please state the complete requirements at the outset instead of adding them in bits and pieces)

    Code:
    Sub ExtractNumbers()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet3")
      m = wshSrc.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      For s = 1 To m
    	If wshSrc.Cells(s, 1) Like "SR Contract*" Then
    	  t = t + 1
    	  wshTrg.Cells(t, 1) = Val(Mid(wshSrc.Cells(s, 1), 14))
    	End If
    	If wshSrc.Cells(s, 12) = "Total:" Then
    	  wshTrg.Cells(t, 2) = wshSrc.Cells(s, 19)
    	  wshTrg.Cells(t, 3) = wshSrc.Cells(s, 20)
    	End If
      Next s
    End Sub

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794946' date='25-Sep-2009 15:20']Here you go (next time, please state the complete requirements at the outset instead of adding them in bits and pieces)

    Code:
    Sub ExtractNumbers()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet3")
      m = wshSrc.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      For s = 1 To m
    	If wshSrc.Cells(s, 1) Like "SR Contract*" Then
    	  t = t + 1
    	  wshTrg.Cells(t, 1) = Val(Mid(wshSrc.Cells(s, 1), 14))
    	End If
    	If wshSrc.Cells(s, 12) = "Total:" Then
    	  wshTrg.Cells(t, 2) = wshSrc.Cells(s, 19)
    	  wshTrg.Cells(t, 3) = wshSrc.Cells(s, 20)
    	End If
      Next s
    End Sub
    [/quote]


    Thanks, Hans-Sorry, will do!

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794946' date='25-Sep-2009 15:20']Here you go (next time, please state the complete requirements at the outset instead of adding them in bits and pieces)

    Code:
    Sub ExtractNumbers()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      Set wshSrc = Worksheets("Sheet1")
    
    
    OOps, I made a mistake.  The second batch of totals should be in column U and not column T.  What do I need to change?  Sorry!
      Set wshTrg = Worksheets("Sheet3")
      m = wshSrc.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      For s = 1 To m
    	If wshSrc.Cells(s, 1) Like "SR Contract*" Then
    	  t = t + 1
    	  wshTrg.Cells(t, 1) = Val(Mid(wshSrc.Cells(s, 1), 14))
    	End If
    	If wshSrc.Cells(s, 12) = "Total:" Then
    	  wshTrg.Cells(t, 2) = wshSrc.Cells(s, 19)
    	  wshTrg.Cells(t, 3) = wshSrc.Cells(s, 20)
    	End If
      Next s
    End Sub
    [/quote]

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change 20 to 21.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794957' date='25-Sep-2009 15:50']Change 20 to 21.[/quote]

    Perfect-thanks again!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For the future: in code such as Cells(s, 20), s is the row number and 20 is the column number. Column A = column number 1, column B = column number [s]3[/s] 2 etc. So if you need to provide for another column, you can easily find the number needed.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794960' date='25-Sep-2009 15:55']For the future: in code such as Cells(s, 20), s is the row number and 20 is the column number. Column A = column number 1, column B = column number 3 etc. So if you need to provide for another column, you can easily find the number needed.[/quote]

    Hans,
    Wouln't column B = 2?

Page 1 of 2 12 LastLast

Posting Permissions

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