Copy data to new sheet only if it meets multiple criteria?

[twirth]

Hello,

I am working with a 13,800 row spreadsheet containing property tax data. I have a column that indicates increases or decreases for the tax year. Not all rows in that column will have a value as not all parcels will have a valuation change. If they do have a change, I need to pull this data onto a new sheet.

However, before I pull the data, I need to make sure it's only the changes for a specific taxing district. A separate column in the same spreadsheet contains names of the taxing districts that must be referenced before pulling the data for the increase/decrease.

In short....I need to be able to look through 13,000 rows for a specific taxing district (Alma Township, Byron Township, etc), if it finds a match it needs to look in another column for any changes in valuation, and if there are any changes - return data from another column in that row that describes the parcel.

I plan to enter the taxing district name in a fixed cell near the top of the sheet- and just reference that one cell in all the formulas - that way whenever that one cell changes for taxing districts, the formulas can continue to just reference that one cell.

Any help is greatly appreciated!

 

[agihcam]

Welcome to the board.
Good explanation.but, you need to provide more info regarding ranges, columns, etc.

 

[twirth]

Sorry for the lack of info - newbie syndrome!

I'll try to to make this quick...

1) Results Sheet - this is the "database"
Column "A" = Parcel
Column "B" = Taxing_District
Column "C" = Value_Change

2) Changes Sheet - this is more of a report than a database
First three rows are header rows with title, column headings, etc. Cell C3 will be "reference cell" in which to enter the name of the taxing district the report will be compiling information for.

Below header rows, in Column A, we'll say row 5, I need to enter a formula that will go back into "Results" sheet, check column b "Taxing_District" for the text name of the taxing district typed in cell C3 of the "Changes" Sheet, then check column c "Value_Change" for any value not equal to zero, and if both of these criteria are met, return the data in Column a "Parcel" from the "Results" sheet into column a, row 5 of the "Changes" sheet, but I need it to return ALL changes for that taxing district - not just the first or last one it finds.

To do this "long hand" I would sort all 13,800 rows by column b "Taxing_District" and then just look through all the rows for anything with a value other than zero in column c, then copy the info needed from the rows (like the parcel number) into the "Changes" sheet. I'm just concerned that doing it by hand could result in errors!

Does that help? Again - thanks for any help!!!

 

[agihcam]

Hi -
Not sure for the formula approach.
assuming the sheet was named Result & Changes
right click the sheet Changes > view code then paste these codes then close the VBE. the code will trigger when the user put values in C3 of sheet Changes then populate columnA to columnC based on the values found in sheet Result when the columnC is greater than 0.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$3" Then Exit Sub
Sheets("Changes").Range("a4:c" & Rows.Count).ClearContents
With Sheets("Result").Columns("b")
    Set c = .Find(Sheets("Changes").Range("c3").Value, , , xlWhole)
        If Not c Is Nothing Then
            f = c.Address
                Do
                    If c.Offset(, 1) > 0 Then
                        Sheets("Changes").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 3).Value = c.Offset(, -1).Resize(, 3).Value
                    End If
                    Set c = .FindNext(c)
                Loop Until f = c.Address
            Else
                MsgBox "No results to display!", vbInformation + vbOKOnly, "No data found!"
        End If
End With
End Sub

 

[jimbeaurama]

Pulling an Array

I have a very similar business issue and am interested in how this works. If you could explain how the code works, I would appreciate it. Is this pulling in a dynamic range of data? I assume the "ClearContents" line will clear the results from a previous pull? The sub then loops, pasting data row by row, until the criteria are exhausted?

Thanks for the explanation. My brain hurts!

 

[agihcam]

I've added here some comments.

Private Sub Worksheet_Change(ByVal Target As Range) 
'the code will trigger only when the user change values in C3 only
If Target.Address <> "$C$3" Then Exit Sub 
If target.value="" then exit sub
'This clear the data in destination sheet ( Changes )
Sheets("Changes").Range("a4:c" & Rows.Count).ClearContents 
'This line of code will set the range to find the data
With Sheets("Result").Columns("b") 
    Set c = .Find(Sheets("Changes").Range("c3").Value, , , xlWhole) 
        If Not c Is Nothing Then 
            f = c.Address 
                Do 'loop until all criteria gad been met
                    If c.Offset(, 1) > 0 Then 
'copy the data found based on criteria to Changes sheet
                        Sheets("Changes").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 3).Value = c.Offset(, -1).Resize(, 3).Value 
                    End If 
                    Set c = .FindNext(c) 
                Loop Until f = c.Address 
            Else 
                MsgBox "No results to display!", vbInformation + vbOKOnly, "No data found!" 
        End If 
End With 
End Sub

Hope it helps.

 

[jimbeaurama]

Thanks. I will give it a try.