Results 1 to 15 of 18
Thread: Extrapolate Numbers

20090925, 14:14 #1
 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.

20090925, 14:17 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20090925, 14:21 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794928' date='25Sep2009 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 worksheetsthe first showing the original data, and the second showing how I would like it on the new worksheet.

20090925, 14:27 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794928' date='25Sep2009 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

20090925, 14:48 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20090925, 14:54 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794938' date='25Sep2009 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

20090925, 14:57 #7
 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

20090925, 15:11 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Great work Hansworks 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.

20090925, 15:20 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20090925, 15:23 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794946' date='25Sep2009 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
Thanks, HansSorry, will do!

20090925, 15:32 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794946' date='25Sep2009 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

20090925, 15:50 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Change 20 to 21.

20090925, 15:52 #13
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794957' date='25Sep2009 15:50']Change 20 to 21.[/quote]
Perfectthanks again!

20090925, 15:55 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20091005, 14:44 #15
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='794960' date='25Sep2009 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?