Extract Certain Data Based On Conditions

Bennymacs · Jun 23rd 2009

Hi there,

I am hoping someone can suggest the best method to extract certain data from a column of values.

The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list.
I have manually entered the info for the 1st June as an example.

Let me know if this isn't clear!
Thanks, Ben

dodger7 · Jun 23rd 2009

Re: Extract Certain Data Based On Conditions

Hi Ben

by moving the headers in your results sheet to the same positions as they are in the data sheet, the following code should work for you.

Code

Sub run()


Dim ws As Worksheet
Dim ws2 As Worksheet


Set ws = Sheet11
Set ws2 = Sheet1


For r = 5 To 194
For c = 5 To 35


lrow = ws2.Cells(65535, c).End(xlUp).Offset(1, 0).Address


val1 = ws.Cells(r, c).Value
If IsNumeric(val1) Then
    If val1 > 0 Then
        ws2.Range(lrow).Value = ws.Cells(r, 3).Value
    End If
End If


Next c
Next r


End Sub

Display More

I attach a working example for your reference.
Hope this helps
Jamie

AAE · Jun 23rd 2009

Re: Extract Certain Data Based On Conditions

Below is a slight modification of the code Dodger7 provided. It just adds a bit flexibility by counting the rows and columns rather than using a fixed number. You may have more or less than 190 rows (models) and the number of days in a month will vary from 28 to 31.

Code

Sub run()


    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim iRows As Long
    
    Set ws = Sheet11 'Data Sheet
    Set ws2 = Sheet1 'Results Sheet
    
'added count of rows and columns
    With ws
        iRows = WorksheetFunction.CountA(.Range("C5", .Range("C65536").End(xlUp)))
        iCols = WorksheetFunction.CountA(.Range("E3", .Range("IV3").End(xlToLeft)))
    End With
    
    For r = 5 To iRows 'added variable here
    For c = 5 To iCols 'and here
    
    lrow = ws2.Cells(65535, c).End(xlUp).Offset(1, 0).Address
    
    val1 = ws.Cells(r, c).Value
    If IsNumeric(val1) Then
        If val1 > 0 Then
            ws2.Range(lrow).Value = ws.Cells(r, 3).Value
        End If
    End If
    
    Next c
    Next r
    
    Application.ScreenUpdating = True


End Sub

Display More

Bennymacs · Jun 23rd 2009

Re: Extract Certain Data Based On Conditions

Thanks both - that's really useful and think it will achieve what I need

How difficult would it be to manipulate the code so that headers in the two sheets don't need to correspond?

dodger7 · Jun 23rd 2009

Re: Extract Certain Data Based On Conditions

Hi Ben

Really simple - sorry never realised that you wanted headers in that specific position. Just change the line

Code

lrow = ws2.Cells(65535, c).End(xlUp).Offset(1, 0).Address

to

Code

lrow = ws2.Cells(65535, c-2).End(xlUp).Offset(1, 0).Address

and it should work fine. Example attached.
This is presuming your headers will be in the position of the first spreadsheet you attached.
If you headers are going to change position, please let me know.

Hope this helps
Jamie

Dave Hawley · Jun 24th 2009

Re: Extract Certain Data Based On Conditions

Have you considered a PivotTable based off a Dynamic Named Range?

Bennymacs · Jun 24th 2009

Re: Extract Certain Data Based On Conditions

Thank you for your help on this matter - I'm trying to adapt the code in the attached workbook but my limited knowledge means I'm stuck with exactly what I need to achieve.

I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)

Hope someone can help...?

Extract Certain Data Based On Conditions

Participate now!

Share