I have two lists about properties in a specific area.
One has general property details, the other has owners names. Both have the property's address.
How do I combine the two lists so I have the names and the property details on one list?
I have two lists about properties in a specific area.
One has general property details, the other has owners names. Both have the property's address.
How do I combine the two lists so I have the names and the property details on one list?
If you have Excel 2010 or later version, you can do a simple table merge in Power Query. Here is the Mcode
Attached is a file showing it.Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
If you do have Power Query then stop reading, Alan's solution
above is much simpler. If you don't then here's an alternative.
Observations: There are 200 properties of which only 65 have listed owners (occupiers). Some properties have multiple (up to 4) occupants.
Is this a one-off merge or will it need to be repeated on a regular basis? Below are a series of manual steps to merge the files, but if this is something that has to be done regularly then a VBA solution would probably be more appropriate.
The end result of the merge defined here is the insertion of an additional 8 columns A:H on the properties list which are populated with up to 4 contact/phone# pairs per property.
Steps:
- Open victoria property 2 bed 5 year.csv
- Open Victoria owners 2 bed 5 years.csv
- Drag the tab of one workbook onto the other so that both worksheets are in one workbook (just my personal preference)
- Rename one tab "owners", the other tab "properties" - the brief names make it so much easier to see what's going on with formulas.
- SaveAs the workbook as "victoria.xlsx"
- On the "properties" worksheet insert 8 columns to the left of the current col-A. Label the columns in row-1: Contact-1, Phone-1, Contact-2, Phone-2, Contact-3, Phone-3, Contact-4, Phone-4.
- Enter the following array formula in properties!A2 - commit with CTRL-SHIFT-ENTER
Formula:Please Login or Register to view this content.- Copy A2 to C2, E2, G2
- Enter the following regular formula in properties!B2
Formula:Please Login or Register to view this content.- Copy B2 to D2, F2, H2
- Copy A2:H2 down to about row 250
That's it - you should now have up to 4 contact names and phone numbers for each property. If there are blanks in all 8 columns then there is no corresponding owner/occupier for that particular address.
The attached workbook implements the above steps.
Last edited by GeoffW283; 03-22-2019 at 02:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks