How to find a change in data

xdriver

New Member
Joined
Mar 21, 2014
Messages
36
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

xdriver

New Member
Joined
Mar 21, 2014
Messages
36
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

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>
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
AddressLast Sale DateLast Sale PriceMapref
[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-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>
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
AddressLast Sale DateLast Sale PriceMapref
[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>
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Mar 21, 2014
Messages
36
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,235
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top