Hi all,
Background to what I was doing: I had a list of customers, and some of these customers shared the same address because they live in the same household. I did a de-dupe selection because I only wanted to select one person from each household. In some cases, I now have updated addresses (if they changed address) for that one selected client from each household. I want to then go back to the original list, and apply that new updated address across the other customers that shared the same original address.
Example table form:
This is all made-up data. My actual file has tens of thousands of rows, therefore I used the original combined address, as the streets could appear multiple times for multiple households.
Essentially I need some logic that will copy the new street, new city and new postcode that I have for the lead customer, to those customers that share in common the old combined address.
Can explain further if necessary but hopefully this makes sense.
Thanks!
@HenryG95 You might consider using a secondary helper table which translates the old address to the new. The complicating factors in this approach are 1) what to do when either a new customer moves into the old address and 2) how to account for only one member of the family moving to a new address. Ignoring the second complication, this could provide one of many possible solutions:
1) create a family group column in your original data based on last name
=MID(A2,SEARCH(" ",A2)+1,65)2) Instead of entering the new address in the original table, enter it into the helper table
3) Use a formula looking for a match on both last name and original address to bring over the new address. This formula would be entered in the New Street column in your original table (of course, replacing the static text with the table references in the original. Doing the same for New City & New Postcode. OR, you could leave the new address columns in place and create three new columns for New Family Street, New Family City & New Family Postcode, particularly if you want to use power query to maintain your helper table (step 4)
=XLOOKUP("Andrews25 Privet DriveLondonB1 2XR",Table1[Last Name]&Table1[Old Address(full)],Table1[New Street])Helper table & lookup formula
4) of course, since you have tens of thousands of records, you could use power query (Get Data/From Range) to pull in the original table, group by last name, Original Combined Address, New Street, New City, New Postcode, then filter out blanks in the new address fields. This should give you a starting point (A new helper table on a separate tab) of unique last name & old address combinations which also have a new address.
Happy to elaborate more if you're not familiar with power query.
Hope this helps get you started.
Accepted Solutions
@HenryG95 You might consider using a secondary helper table which translates the old address to the new. The complicating factors in this approach are 1) what to do when either a new customer moves into the old address and 2) how to account for only one member of the family moving to a new address. Ignoring the second complication, this could provide one of many possible solutions:
1) create a family group column in your original data based on last name
=MID(A2,SEARCH(" ",A2)+1,65)2) Instead of entering the new address in the original table, enter it into the helper table
3) Use a formula looking for a match on both last name and original address to bring over the new address. This formula would be entered in the New Street column in your original table (of course, replacing the static text with the table references in the original. Doing the same for New City & New Postcode. OR, you could leave the new address columns in place and create three new columns for New Family Street, New Family City & New Family Postcode, particularly if you want to use power query to maintain your helper table (step 4)
=XLOOKUP("Andrews25 Privet DriveLondonB1 2XR",Table1[Last Name]&Table1[Old Address(full)],Table1[New Street])Helper table & lookup formula
4) of course, since you have tens of thousands of records, you could use power query (Get Data/From Range) to pull in the original table, group by last name, Original Combined Address, New Street, New City, New Postcode, then filter out blanks in the new address fields. This should give you a starting point (A new helper table on a separate tab) of unique last name & old address combinations which also have a new address.
Happy to elaborate more if you're not familiar with power query.
Hope this helps get you started.