joining data in vertical and horizontal formats
I am trying to provide single sources of entry for loan data information that can feed all reports about that data. Currently, the primary source for some of the loan data is entered into an Excel spreadsheet with 'property' as the primary key. This file contains alot of other unrelated data points. The loan data is listed horizontally in this file and some 'properties' have multiple loans. The primary loan identifier and the data related to them are differentiated from one another by adding a 1, 2 or 3 to the column name. I want to join this loan data to data in another Excel spreadsheet that is the primary source for the remaining data about these loans but the key to this spreadsheet is the loan identifier, not the property, and each loan in this spreadsheet is listed on a separate vertical line. Is there a way to flip the relevant data in the horizontal file to vertical so that it can be joined with the data in the vertical file?