How to find a change in data

[xdriver]

I am familiar, to an extent, with index-match and is what I am using in my spreadsheet for most of the equations. My spreadsheet is using public housing data for my local community along with manually inputed data such as what school, what neighborhood, and if it is on the water or not. Every few months I download new data and use index match to reassign the manually inputed data to the new public record for ownership. I utilize a constant, the map reference as a key, like you would in access. For instance, it is formatted as such "M:00000004 P:00000031" and the new data info has that same map reference associated with the same address and info as the previous input.

What I would like to do is actually mark the changes in ownership by comparing the most recent sale date to the previous import sale date and then place a "1" in a column if it the date has changed. Any suggestions would be helpful, and I am happy to try to explain more if necessary. Thank you.

 

[Fazza]

One approach that I think matches the requirement is using a query.

UPDATE ExistingTable E INNER JOIN NewData N ON E.KeyField = N.KeyField
SET E.Owner = N.Owner
WHERE N.SaleDate > E.SaleDate

I am happy to explain more if necessary. regards

 

[xdriver]

One approach that I think matches the requirement is using a query.

UPDATE ExistingTable E INNER JOIN NewData N ON E.KeyField = N.KeyField
SET E.Owner = N.Owner
WHERE N.SaleDate > E.SaleDate

I am happy to explain more if necessary. regards

There are over 14k properties. Sometimes the data changes the address from "rd to road" or similar, but the mapref stays constant unless the address is deleted and new addresses and corresponding mapref are added, such as a subdivision etc. In the below data for example, 13 Frost and 29 Cabot have since been sold. Ideally I would create another column to put a "1" into to indicate a change in sale date from Sheet1, the old values, to sheet2, the new values. (excel 16.10 for mac)

Sheet1 (old values)
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

Unknown 64 bit

A​ B​ C​ D​ [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 1​ Address Last Sale Date Last Sale Price Mapref [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 2​ 29 Charles St 9/25/02​ $173,000​ M:00000035 P:00000169 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 3​ 29 Coachman Ln 5/19/06​ $634,000​ M:00000023 P:00000092 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 4​ 29 Commonwealth Rd $0​ M:00000011 P:00000027 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 5​ 13 Frost St 1/1/04​ $301,000​ M:00000004 P:0000013A [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 6​ 22 Indian Rock Rd 12/31/10​ $335,000​ M:00000004 P:00000096 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 7​ 17 Milford Ave 9/30/97​ $185,000​ M:00000004 P:00000071 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 8​ 29 Cabot St 4/5/96​ $152,500​ M:00000004 P:00000031 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 9​ 29 Clubhouse Ln 7/28/05​ $649,500​ M:00000002 P:0000005D <tbody> </tbody> Sheet: mrexcel <tbody> </tbody>

<tbody>
</tbody>

Sheet2 (new values)

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

Unknown 64 bit

K​ L​ M​ N​ [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 1​ Address Last Sale Date Last Sale Price Mapref [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 2​ 29 Cabot St 2/1/18​ $525,000​ M:00000004 P:00000031 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 3​ 13 Frost St 12/12/12​ $325,000​ M:00000004 P:0000013A [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 4​ 22 Indian Rock Rd 12/31/10​ $335,000​ M:00000004 P:00000096 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 5​ 17 Milford Ave 9/30/97​ $185,000​ M:00000004 P:00000071 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 6​ 11 Cabot St 11/8/04​ $0​ M:00000004 P:00000049 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 7​ 1 Cabot St 10/3/67​ $0​ M:00000004 P:00000046 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 8​ 17 Hovey Ave 4/14/08​ $635,550​ M:00000004 P:00000085 [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 9​ 12 Indian Rock Rd 6/21/99​ $1​ M:00000002 P:0000002G [tr=bgcolor:#FFFFFF][td=bgcolor:[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888"]#888888[/URL] ] 10​ 6-1/2 Cabot St 6/12/09​ $348,000​ M:00000004 P:0000048B <tbody> </tbody> Sheet: mrexcel <tbody> </tbody>

<tbody>
</tbody>

 

[Fazza]

Excel for Mac - I know nothing about.

Though presumably it can do queries & adopt the proposed solution. 14,000 rows should updated in the blink of an eye. Indicated data changes are immaterial as Mapref is the key field.

 

[xdriver]

Excel for Mac - I know nothing about.

Though presumably it can do queries & adopt the proposed solution. 14,000 rows should updated in the blink of an eye. Indicated data changes are immaterial as Mapref is the key field.

Would you mind rewriting the query for me using "sheet1" and "sheet2" as well as mapref as I don't quite understand how to do it. Also, what exactly will the query do? Create a new sheet with the results that have changed? Thanks a ton.

 

[Fazza]

change to sheet names "sheet1" & "sheet2"
change key to "Mapref"

UPDATE [Sheet1$] E INNER JOIN [Sheet2$] N ON E.Mapref = N.Mapref
SET E.YourMarkerField = 1
WHERE N.[Last SaleDate] > E.[Last Sale Date]

Per the question, Sheet1 field (I've nominally called) "YourMarkerField" will be set to 1.