When I was at university, I was working in the Office for Institutional Research. It was a32'>
Step 1: Get a list of unique departure and destination names using the 'de-duplicate' feature
If both sets of numeric data are already formatted in a similar way, such as prices always formatted as $1.00, you can use the Excel consolidate feature (under the ‘Data’ dropdown menu). Open each sheet you plan to use and confirm that the data types you want to consolidate in Excel match. In a new empty worksheet, select ‘Consolidate.’. Just make use of the third party merge cells add-in for Excel. And with the merge cells add-in you can merge data from many cells by using any separator you like (for example carriage return or line break). With this, you can join row by row, column by column, or merge data from the selected cell into one without any loss. Select the data range that you want to use, and then click Kutools Merge & Split Advanced Combine Rows to enable the Advanced Combine Rows dialog box. In the Advanced Combine Rows dialog box, click the column name that you want to combine other data based on. Combine data with the Ampersand symbol (&) Select the cell where you want to put the combined data. Type = and select the first cell you want to combine.
There are thousands of flights by DFID staff in 2011, but only about 300 or so departure or destination locations. In order to merge our data, the first thing we need to find is that list of locations.
I'm just going to start by opening a new sheet in the workbook and copying both columns D and E ('Departure Airport' and 'Destination Airport') into a single column, one below the other, on the same sheet. It gives us about 17000 entries.
I'm going to select the column and then sort it from A-Z, just to confirm there are a lot of duplicates (this step is optional).
And then, also in the Data tab, I will select the 'Remove Duplicates' button, and then approve it to de-duplicate column A only. It notes that there are only 303 unique items after de-duplication.
Step 2: Open global airports data, paste the list of locations and create VLOOKUP fields
Open up the Global Airports data, create a new sheet and copy and paste our list of 303 airport names. It's not necessary to work from this spreadsheet, but I have to keep things contained within single workbooks.
In cell B1, insert an '=' followed by VLOOKUP. A helpful box that explains the formula might popup:
The VLOOKUP (or vertical lookup) function basically takes a value that you want to lookup, searches within a particular table 'array' (a table of data) and returns the value that is in whatever column of the array you want. Its companion function is HLOOKUP, which will search horizontally and look up a number in a given row.
In this case we will lookup the value A1 (which is DFID's name for an airport) in the table array columns A (column A is our airport name, column B is the airport city, column C is the airport country and column D is the IATA code) through D in the other sheet in our workbook called Global Airports, and we want the three-letter IATA code, which is found in column 4. So our function looks like:
The 'FALSE' after the last comma indicates that we want to find an exact match, and not just a close match. I will double click the square in the bottom-righthand corner of the cell to fill down the entire series.
But I also want to check to see if it matches the city. The VLOOKUP function will only match a value with the left-most column in the table array. So to find the IATA code from the city, our function in cell A3 would be:
You'll note that the array now starts from column B and not A, and only looks up the third column, not the fourth.
Merge Same Values In Excel
To fill these two formulae down the series, I select both of them and double-click the square in the bottom-righthand side of the right cell.
Step 3: Copy and paste the values
Merge Data From Multiple Excel Sheets
These VLOOKUP values are now dynamic, and if something in the worksheet changes, they might break. So I always select and copy these values and paste special, paste as values.
Step 4: Further tidying
This has got a number of the cells filled, but not all. In order to finish I take the following approaches:
Duplicate the 'name' column (one will be associated with the airports dataset, one with the DFID dataset). Try editing names like 'Baltimore MD' to simply 'Baltimore' in one column and see if the VLOOKUP now finds it.
- For some airports we get a value of '0' rather than '#NA'. That's because the cell it has found is blank (rather than not having found the cell), so we can try inserting the IACO code instead.
- When in doubt, just Google '[place] airport' and the three-digit code usually comes up on the home screen. Copy and paste that into a spreadsheet.
It's also worth noting that, if you're using the city as the lookup value, we need to be careful. Big cities may have more than one airport, and the lookup will pull whichever one it finds first.
Merge Adjacent Rows With Same Data In Excel
Step 5: Repeat VLOOKUPs to add in latitude, longitude, city, country columns and merge back with DFID data
After I got all of the airport codes, I used those as the basis for a VLOOKUP to pull all latitude, longitude, city and country data into our spreadsheet as well.
Finally, I used the VLOOKUP code back in our DFID flights spreadsheet to lookup departure and destination information. I then copied, and pasted as values that information and deleted the formulae.
I have made this final version of this spreadsheet available on Google Sheets for download to check your work.
How To Merge Same Data In Excel