How to find a change in data

X

xdriver

Board Regular
Joined
Mar 21, 2014
Messages
73
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
One approach that I think matches the requirement is using a query.

Code: 
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
 
Upvote 0
Fazza said:
One approach that I think matches the requirement is using a query.

Code: 
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
Click to expand...

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


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


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.
 
Upvote 0
change to sheet names "sheet1" & "sheet2"
change key to "Mapref"
Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Formula to find Data and Corresponding date and return total sales and total units per item
Replies
4
Views
225
RoryA
RoryA
D
  • Solved
Using VLOOKUp to find a Date in a Data Worksheet
2
Replies
13
Views
258
Alex Blakenburg
A
R
  • Question Question
Freezing/Logging Data from DDE at 1 Minute Intervals
Replies
1
Views
125
Anthony47
A
B
  • Solved
Rate/Day over X Most Recent Entries
Replies
6
Views
133
Eric W
Eric W
P
  • Solved
Date Formatting Issues
2
Replies
11
Views
430
Pacman52
P

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top