Excel Question -Match Data from Two Excel Files that Share Similar Attributes

KenricMcCay

Hello,

I don't know if anyone knows this answer, but I thought I would give it a try here in our GeoNET.  I need to do this first so that I can update it in GIS.

I have two different Excel spreadsheets. They have almost the same data, but it has been updated so some entries are not there anymore or some items have been added.

I have some items that are labeled "I" or "O" for inside or outside in the "E" columns. I need these in my new spreadsheet, and there are 300 or so of them.

The values that are the same and need to match are KVA, LOW_VOLTAGE,COXX, and HIGH_VOLTAGE (rows A, B, C, and D).

I would like to somehow have the formula look at these values from the original sheet, and if they are the same, it will put an "I" or "O" in the field "E" in my new sheet.

Thank you very much for helping me!

RichardFairhurst

In order to do this in Excel you would need to create a new column in both tables that concatenates the four look up columns together first.  They you would use the VLookup formula to fill in column F (since you should insert the concatenated look up column into a new column A)

After inserting a new column A, the formula for the concatenation in cell A2 would be

=CONCATENATE(B2,";",C2,";",D2,";",E2)

Then drag this formula down the column to copy it and increase the row number

The VLookup formula would start in the new sheet's column F2 (I/O) and would be basically:

=VLOOKUP(A2,Sheet2!A$2:F$6,6,FALSE)

The F$6 portion of the formula should have the bold number edited to change it to the last filled in row number in the original sheet.  Sheet2 should match the sheet name of the original data in your spreadsheet.  Then drag this formula down the column to copy it and increase the A2 row number.

Convert the formulas to values before trying to open the spreadsheet in GIS, since GIS does not understand Excel formulas, only hard values.