Playing with your EIDB results
Description:
The Ellis Island Immigration Database is one of the treasures of on-line genealogy. With a little information and a few mouse clicks, you can find the details of your ancestor's passage to America. The One-Step search tools developed by Dr. Stephen Morse and available at JewishGen.org make this process even easier.
However, sometimes finding your ancestor can prove difficult. If your ancestor had a common name, the name was misspelled, or this indexing in error, finding the right entry in the index can prove challenging. The One-Step search tools do not allow you to sort the results, which can make finding your entry difficult. This is why I have arranged a spreadsheet that can interpret the results of the EIDB One-Step searches and allow you to manipulate the results for your individual needs. The spreadsheet is able to:
-
Easily sort the results by surname, given name, age, date, date of birth, or place - or any combination of them
-
Merge multiple searches into a single sheet
- Save your results in a convenient form for you at a later time
-
Add comments and annotations to your searches
Note: Both Ellis Island Immigration Database and JewishGen.org are non-profit organizations. They provide a valuable service that is funded through donations. Please support them. The instructions below are not intended to break any copyright laws, and any manipulation that you do to the data should be for your own personal use. |
How To Make the Spreadsheet:
- Open Microsoft Excel. Open a new, blank spreadsheet.
- In Row 1, enter the formulas in each of the columns
indicated in the table below. Columns A-J should remain blank.
ColumnHeadingFormulaColumn K: --=IF(ISERROR(FIND(",",B1)),"", FIND(",",B1)) Column L: Surname =IF(ISERROR(LEFT(B1,K1-1)), "", LEFT(B1,K1-1)) Column M: Given Name =IF(ISERROR(LEFT(B1,K1-1)), "", MID(B1,K1+1,30)) Column N: --=IF(ISERROR(FIND(",",C1)),"", FIND(",",C1)) Column O: Place 1 =IF(ISERROR(LEFT(C1,N1-1)), IF(ISBLANK(C1),"", C1), LEFT(C1,N1-1)) Column P: Place 2 =IF(ISERROR(LEFT(C1,N1-1)), "", MID(C1,N1+2,30)) Column Q: Year of Arrival =IF(ISBLANK(D1),"",D1) Column R: Age =IF(ISBLANK(E1),"",E1) Column S: Approx DOB =IF(ISERR(Q1-R1),"",Q1-R1) - Copy the EIDB output from the White (New Output), Blue, or Gray One-Step searches. You should highlight the entirety of each line you want to sort - including the number. You do not need to copy the column headings. To copy, press Ctrl-C. Go to your Excel spreadsheet, put your cursor in the next available cell in Column A, and press Ctrl-V to paste. This information from the EIDB should fill columns A-I of your spreadsheet.
- Copy the contents of Row 1, Columns J-S to Columns J-S for each row that output is desired for. Excel will automatically reformat the EIDB results, separating first and last names! Clicking on the links will open your browser and go to the correct record at Ellis Island Immigration Database.
- "Hide" columns B-E, K, and N by highlighting the entire column, right-clicking the mouse, and choosing "Hide." This will unclutter your spreadsheet for easier searching. This step can be skipped.
- The output should look like the image below:
- The data can then be sorted by the new columns. Highlight the Rows that you want to sort - make sure the whole row is highlighted. Choose "Data" from the menu toolbar at the top of the screen and then choosing Sort. Sort by Surname, First Name, Year of Arrival, Age, Town/Country, or Approx Year of Birth by choosing the appropriate columns in the 'Sort By' fields and choosing 'OK.' The results should automatically adjust themselves
- You can use Column J or Column T to put in your comments! Just be sure to sort with your other records, so they don't get separated.
Thank you to Steve Morse for his suggestions and willingness to share his expertise.
Drop us a note:Shawn Weil