Please accept my apologies if there is a similar request asked already, or if this is a profoundly stupid question, any help would be greatly received.
I have two large data sets.
1 is all of the property sales in an area,
2 is all the properties epc ratings by area showing square footage, energy usage etc.
I want to merge the two data sets into one large table so that I can chart the information.
The only common denominator between the two data sets is the address of the property.
What would the formula be to use the spreadsheet 1 (Sold price data) as my base, and add all of the information to it from spreadsheet 2 (EPC Data)?
I would like all the data from spreadsheet 2 added to each line of spreadsheet 1. I guess I need a formula to look up and make sure each full address and post code matches (they are in separate columns), and then add each field from spreadsheet 2 over to spreadsheet 1?
I have attached an example file to show the two data types I have although they will not match as I have taken 10 lines or so from millions of lines of data.
OK, here is what I'd do. In both sheets add an additional, calculated column which combines the data from the address columns, like so (example for the EPC data sheet): =UPPER(TRIM(B2)&"|"&TRIM(C2)&"|"&TRIM(D2)&"|"&TRIM(E2)) UPPER changes everything to upper case and trim removes leading and training spaces. Suppose you inserted a similar formula in column R of the first sheet, then in the second sheet, next to your new column (say column CH) with the above formula you enter this formula in CI2: =MATCH(CH2,'Sold home data'!$Q:$Q,0) These should return row numbers where your matched data is. Finally, in columns to the right of that you can fetch the information you need with a formula like: =INDEX('Sold home data'!H:H,CI2)