It’s easy to put your spreadsheet data on a map with BatchGeo. While Excel is the most common application, this same process also works with Numbers and Google Docs.
Only one column needs to contain location data. For example, you might only have zip codes or city names, or you could have a complete address. The more data you have the better.
To get started, follow these steps:
- Open your spreadsheet
- Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) all your data
- Open your web browser and go to batchgeo.com
- Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
- Check to make sure you have the proper location data columns available by clicking "Validate and Set Options"
- Select the proper location column from each drop down
- Click "Make Map" and watch as the geocoder performs its process
Once all of your rows are geocoded, click “Save & Continue” and enter some details about your map. Provide a title and, optionally, a description. To claim the map as your own, which allows you to edit it later, include your email address.
Now click “Save Map” and you’ll be directed to your map’s unique page.
For more information on how to create a map using excel spreadsheet data, watch our short video.
Comparing two Excel sheets and combining the unique data can be done with the help of macros. There are several macros available for free that can be downloaded from the internet and tweaked according to the requirements.
Although the macros are not required to be written from scratch, some knowledge of programming concepts such as if -else- loops and nested loops can prove useful when writing macros for Excel office software. Redundant data can be removed from the Excel worksheets by using macros that can compare and combine unique data in Excel sheets.
Example
There are two Excel sheets, A and B. There is redundant data in both sheets as well as unique data. The aim is to combine both with no redundant records:
- A+B-Redundant = Unique Complete Data.
The issue arises when it is not possible to copy and paste, and then filter unique, because the total data is more than 68000, which is more that 65K rows per sheet. How is it possible to use the IF(...) command?
Solution
Without going into a lot of details, there are several ways to combine specific data from numerous sources of RAW DATA. The easiest way (once you understand how) would be to use Microsoft Access. Here you will need to use the 'help options' for the specific process; but what you will be doing is "mapping" two or more documents together which share 1 category of redundant information (i.e. NAME, PART NUMBER, etc.).
For example, if each the worksheet has a person's NAME, along with another column or information. you would map the NAME in each worksheet, then select the additional fields you want captured in your output. Besides the Access Help engine, you can repost your help request here under 'mapping & Linking).
If you want to stay in Excel, try using an Index Match statement.
=INDEX(SheetA!C:C,(MATCH($A2,SheetA!A:A,0)))
Type this formula in a blank cell within the row/entry you are comparing. This formula will look in SheetA through all data within column A. If data is matched to the identifier ($A2), it will then copy the data entered in column C (number or text). #N/A is reported if no match is found. SheetA
Part1 $2 A xxx Part2 $3 B yyy Part2 $4 V zzz
Sheet B
Part1 50 MN type your formula Part2 60 WI type your formula Part2 70 MO type your formula
The cell with the formula will collect "A","B" and ,'V" from the first sheet. Copy and change the formula to collect data from the columns you want (skip the redundant/superfluous ones).
For best results, lock in your 'identifier' cell (in this case $A2)
To use a formula solution, data within sheets needs to be organized the same way because you are defining which 'columns' to match and pull information from.
Any more excel questions? check out our forum!
How does the vLookup formula work?
Excel's vLookup formula pulls data from one spreadsheet into another by matching on a unique identifier located in both spreadsheets. For example, we want to add a column for email address but that data exists on a separate spreadsheet. vLookup can pull email addresses from Spreadsheet 2 into Spreadsheet 1 by matching CampusID 555123123 in both spreadsheets.
How to Use the vLookup Wizard
Locate where you want the data to go. Click that cell only once.
At the top, go to the Formulas taband click Lookup & Reference
Select vLookup
Excel's vLookup wizard will pop up. We'll walk through each part of the formula.
Lookup_value
Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student is the email in one row and Student in the other, Excel will choose one of the emails for you.
Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:
Finally, copy and paste the formula to pull emails for the rest of the column.
(Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
vLookup Shortcut
If you feel comfortable with the vLookup tool instructions above, you can type the formula directly in the cell instead of using the wizard.
Type the beginning of the formula: =VLOOKUP(
The formula guide will appear below.
(Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
Follow the guide and enter each value. Remember to insert a comma between each value.
Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
=VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE)Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)
Advanced Tip on Relative References
The position of the lookup value (Unique ID) in relation to the vLookup formula is maintained when you copy and paste. If you paste the formula one cell down (to E3), it looks up the Unique ID that is also one cell down (B3). The same is true when copying right, left or up.
In other words, the formula will stay x number of columns and y number of rows away from the lookup value – no matter where you paste the formula. In our example, the formula is the fourth column from the CampusID and in the same row. No matter where you paste the formula (in this example), it will always look up the cell that is the fourth cell to the left in the same row.
However, it is possible to lock cells in place by inserting 1 or more $ signs. This means, no matter where you paste the formula, it will always reference the same cell.When copying and pasting the formula, use the $ sign to lock in cells.
To lock in the lookup value in cell B1, insert $ signs before the column and the row:
=VLOOKUP($B$1,'[Spreadsheet2.xlsx]SheetName'!$B$1:$E:$11,3,FALSE)To lock in the column only, insert a $ before B only.
To lock in the row only, insert a $ before 1 only.
Need More Information?
If you have questions about this document, please contact the Office of Data Management and Analytics Services (ODMAS, //data.wisc.edu/) at .