Combine 2 lists about the same data

If you have Excel 2010 or later version, you can do a simple table merge in Power Query. Here is the Mcode

Please Login or Register  to view this content.

Attached is a file showing it.

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:

1. Open victoria property 2 bed 5 year.csv
2. Open Victoria owners 2 bed 5 years.csv
3. Drag the tab of one workbook onto the other so that both worksheets are in one workbook (just my personal preference)
4. Rename one tab "owners", the other tab "properties" - the brief names make it so much easier to see what's going on with formulas.
5. SaveAs the workbook as "victoria.xlsx"
6. 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.
7. Enter the following array formula in properties!A2 - commit with CTRL-SHIFT-ENTER
   
   Formula:

   Please Login or Register  to view this content.

8. Copy A2 to C2, E2, G2
9. Enter the following regular formula in properties!B2
   
   Formula:

   Please Login or Register  to view this content.

10. Copy B2 to D2, F2, H2
11. 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.