Excel, Schmexcel, Dig Up Those Relatives
Table of Contents :
- Appendix A: Using Excel Spreadsheets to Search the Database
- Appendix B: Instructions for Copying Search Results to an Excel Spreadsheet for Analysis
Question: When you have identified 100 likely relatives on the 1858 Revision List, how do you know they are really your relatives? No one ever talks about this at Jewish genealogy conferences and meetings - but a professional genealogist looks for sufficient, competent, evidential matter that is persuasive by its preponderance. Do you have birth, marriage, death, or corroborating evidence to validate that these individuals are in fact relatives of yours?
Answer: I use what documentation I already have, coupled with various levels of analysis of the spreadsheet data, a lot of logic, what meager knowledge I have of life in the Pale of Settlement, and some occasional assumptions. You asked how I could be certain that people on the list were my relatives. Let me give you an example.
My great-grandparents, Faivel and Ida Dora Heilig, and 10 of their children left Lithuania at various times during the time period 1900-1902. The ships’ manifests for Ida Dora and the children listed Uzventis as their town of origin.
Faivel’s younger brother, Kalman, went to Montreal. Canadian immigration records show Uzventis as his town of origin. His death certificate listed his parents’ names as Hirsh and Yannie.
Faivel’s 1920 will named a sister, Hinda, then living in "Russia." Faivel settled in a small town in Maryland at the urging of his niece who was already living in that town. Her mother was Faivel’s older sister who never left Lithuania. On the niece’s death certificate her mother’s name was given as Sarah.
One of Faivel’s daughters married soon after arriving in Maryland and had a child in 1903 who died at 3 months of age. The death certificate for that child, as completed by a local non-Jewish official, listed "Haylik" as the maiden name of the mother. Faivel and Kalman were born sometime around 1860, +/- a couple years, so they would not likely appear in the 1858 list, but the old family stories indicated that there was an older brother (name unknown).
So, based upon these already known and documented facts, I knew I needed to look for Hirsh and Yannie in Uzventis, with a surname pronounced as "Hay-lik," with at least two daughters named Hinda and Sarah, and at least one son. I sorted the 1858 list by shtetl name, then by surname, then by father’s name. Looking at the Uzventis list for surnames that would sound like "Haylik" I found Gelin. The Cyrillic "G" was often used to record the Hebrew or Yiddish "H" sound. A cursive Cyrillic "n" looks very much like the "k" and the two letters could easily be confused in an old, handwritten document. From the list, Girsh Abe Gelin, Hiene Gelin (spouse of Girsh), Ginde Gelin (daughter of Girsh), Chaie Sora Gelin (daughter of Girsh), and Aron Gelin (son of Girsh) identified a family unit in Uzventis. No other surname came close to Haylik, so Gelin was probably Gelik and pronounced "Haylik." Girsh is pronounced "Hirsh," Hiene would be pronounced "Hyeh-nee" (or "Yannie"), Ginde is "Hinda", and Chaie Sora is "Sarah." As a result of this careful record sifting and sorting, I now had identified Hirsh and Yannie Haylik and their children, which included two daughters, Hinda and Sarah, and one son, Aaron, all from Uzventis!
Looking through all of the other "Gelin" entries, I was able to identify Girsh’s father, Leyb, and Zusman as Leyb’s father. Then I identified Fayvush as Leyb’s brother, etc.
Trying to identify parents and siblings of married women requires some logical assumptions. Hiene’s father’s name was listed as Wolf. There was no Wolf in Uzventis old enough to be Hiene’s father, but there was another individual, of an appropriate age to be Hiene’s brother, whose father was listed as Wolf. His name was Markus Kalman. It was not unusual to name a child with a mother’s maiden name, so I feel quite certain that, as her youngest son would later be called Kalman, Hiene was very likely the daughter of Wolf Kalman. Since it was very common for an individual to be known by his or her middle name (e.g., Faivel was Schriga Faivel, and his sister, Sarah, was Chaie Sora), then Kalman’s full name may well have been Wolf Kalman Gelik. If I can find documentation that Kalman’s first name was Wolf, that would validate my assumption.
I used similar methods to identify the parents of my GGM, Ida Dora (maiden name Sher), and was able to trace back to her GGGF, Movsha Sher. I could then branch out at each level to identify siblings, nieces and nephews. In those instances where I had to make a logical guess to attach a married woman to her likely father, I could usually justify my conclusion with additional information based on naming patterns. For example, the woman appeared to be named after her grandparent or great-grandparent and her children appeared to be named after her deceased parents or grandparents.
If and when additional records surface pertinent to my ancestors, I hope to find additional information that would impact on the validity of the assumptions I have made. Until then, where appropriate I shall list some relatives as "probable" and hope that I can connect with other individuals who may have "sufficient, competent, evidential matter which is persuasive by its preponderance" relevant to my "probables."
I apologize for the long answer to your query, but you did ask! And even if I am not correct in all my identifications, it’s fun trying to solve this big puzzle.
For those of you who have not had much experience working with spreadsheets, following are a few hints. The following is based on Excel, but most spreadsheet programs have the same features. You first may need to learn how to copy search results from your browser window to an Excel spreadsheet. If so, go to Instructions.
First, on a floppy disk make a backup copy of the initial compressed ("zipped") file that you received from LitvakSIG. Do the same for all subsequent installments that will follow. Immediately after you open and expand the compressed spreadsheet file, save the original expanded file in another location on your hard drive (use the "Save As…" command in the Edit menu) to keep as an archive file. Then use "Save As…" again to save a copy to use as your working file. Give the working file a name different from your archived file to avoid confusion. If you accidentally corrupt the working copy of the spreadsheet, you can always make another copy from the archived original. As you get additions to the data, you should first save your archive copies, then copy the new data to paste into your existing working copy.
Approaching your working spreadsheet file, don’t be intimidated by the thousands of entries. You need to break the file down into more manageable and meaningful worksheets. First, reduce the size of your worksheet to a more manageable, yet still legible, 75%. Click on the arrow of the "Zoom" box at the far right of your top taskbar and select 75%. This will allow you to see more of the information at one time.
Probably the next most useful initial procedure is to sort by town name. Click on Data (on the menu bar at the top of your Excel screen), then on Sort. In the Sort window that pops up, first click on the circle next to "Header row" in the lower left portion of the Sort window. Then go to the top selection box under "Sort by" and click on the small arrow to bring up the list of header titles. Click on "Town," and then click on the circle next to "Ascending" to sort from A to Z. Then click the "OK" button at the bottom.
At this point it may be helpful to make separate worksheets for each town of interest. To insert a worksheet, click on "Insert" (at the top of your screen), then click on "Worksheet." Repeat this procedure to add as many worksheets as you need. There is a method to add multiple sheets at one time but, if you are a novice with spreadsheets, just do the simple, one-at-a-time method. To label a worksheet, double-click on its label tab at the bottom (there will be default labels…Sheet1, etc.), then type in your own label (e.g., the shtetl name). If you want to re-order your worksheets, simply click on the label tab and drag it along the row of labels to the position you want. Now you should put a header row on each of your new worksheets. Go to the main worksheet (labeled "1858Revi") and click on row label "1" at the top left. This should highlight the entire row. Copy the row by either (a) clicking on the copy icon on the task bar or, (b) click on "Copy" in the Edit menu. This will result in a moving, dotted line appearing around the highlighted row. Now go to each of your new worksheets, click on the row label "1" in each, and then click on either (a) the paste icon on the task bar or, (b) click on "Paste" in the Edit menu. You do not need to return to the 1858Revi sheet between pasting the header rows onto each new worksheet.
Now transfer the appropriate data to each shtetl worksheet. Return to the main worksheet and scroll horizontally until you can see the "Town" column. Scroll vertically until you find the first entry for a given town of your interest, then click on the row number at the left and hold the right mouse button down. Drag down the row numbers until you get to the last entry for that town. This should highlight all the entries for that town. Next click on "Copy" (from the task bar or Edit menu). Go to the worksheet labeled for that town, and click on the first open cell under the "Page#" heading. Then click on "Paste" (from the task bar or Edit menu). The width of the columns on the new worksheet will differ from the original. To resize the columns, move your mouse pointer to the row of lettered column labels at the top of the worksheet. As you slide the pointer along the letters, you will notice, as the pointer crosses the boundary between columns, it changes shape to become a cross with arrows at either end of the horizontal member. Click the right button and hold it down as you move the column boundary to the size you want. You now have a worksheet for all the entries for that town. As new installments of the data become available, you can easily add entries to a town worksheet by following a similar procedure.
Please, remember to SAVE YOUR FILE FREQUENTLY, especially after any sorting or data entering operations! You can never save your file too often…that way you’ll never be frustrated by computer lock-ups, power shortages, etc.
You are now ready to begin your search in earnest. Having done your original sort by town name, you may recall there were three successive sort boxes available. A good initial step is to sort by "Surname," then by "Given" name, then by "Father." Then you can look for specific individuals more easily. If you don’t find someone under a given surname, remember that names often changed and spellings could vary. If you know the father’s name for the individual you are trying to find, do another sort. For example, sort first by Father, then by Surname, then by Given name. If you aren’t finding an individual in the shtetl you thought he came from, go back to the 1858Revi worksheet and do your sorting on that sheet.
You may want to add a worksheet for each family grouping so you can add individuals as you find them. I found it helpful to have a "miscellaneous" worksheet where I could store listings of individuals who were possible relatives. I entered my own comments for each such individual beginning after the last column of the original data list.
Printing parts or all of a worksheet may help you, as you can see more of the information at one time and make notations on the paper. If you want to print only part of a worksheet, highlight that portion and select Print from the File menu. Then click on the circle next to "Selection" at the bottom left of the Print window. Next, click on "Preview" in the bottom left corner. You will probably find the landscape view more useful, so click on "Setup" at the top of the preview window and choose "Landscape." If you find that your selection is too wide to fit across a single sheet of paper, you have several options. One option is to use legal paper. In that case, don’t forget to select the "legal" paper size on the "Setup" screen. A second option is to resize the printed selection. Again on the "Setup" screen of your print preview, if your selection is no more than about 30 lines you can click on "Fit to:" and select one page wide by one page tall. For every increment of 30 lines or so, increase the "Fit to:" by an additional page tall. If fitting the selection into one page wide makes the font size too small, you may want to remove from the printout those columns that you may not need. For example, you may not need initially to look at "Page #" or "Guberniya" or "Record Publication." An easy way to do this is to "hide" columns on a worksheet. Go back to your worksheet view and click on the lettered column label for a column you do not wish to appear on the printout. This will highlight the entire column. If you want to hide more than one column, hold the "Ctrl" key down and click on the other columns. Then click on the "Format" menu, then on "Column" and then on "Hide." The selected columns will disappear from your worksheet view. (Don’t worry, they still exist, but they are just "hiding.") Now go back to the Print operations as before. When you want to see the hidden columns again, just go back to "Format" and "Column" and select "Unhide."
On a large piece of paper, pencil in a rough chart, or "tree," of each family grouping, including individuals whom you have identified as possibly related. Write the approximate birth year next to each individual, as grouping people by similar ages and generation levels can often give you helpful clues. A useful method of identifying married sisters is to sort by "Father" and then by "Age1850" or "Age1858." This will show you individuals of similar ages who may be siblings. Of course, if your families were similar to mine, siblings could be 27 years apart!
I hope my methods help some of you get started. I’m sure many of you will develop your own methods for analyzing the data, probably superior to mine. I hope everyone finds big bunches of relatives so we can pool our findings and look for common relatives.
To get the search results into an Excel spreadsheet is relatively easy. I will describe the process in simple terms to accommodate users with limited spreadsheet experience.
In the browser page, click and drag to highlight the entire search table results (or specific portions of interest), then press to copy (or Edit/Copy from the taskbar). Open Excel to a new spreadsheet, click on the top left cell (A1) - or any other cell where you want the top left corner of the data to appear - then "Paste" (press , or click on the "Paste" icon in the toolbar, or select Edit/Paste from the taskbar). Immediately after the Paste operation is done, save your new spreadsheet (File/Save As). After each subsequent data entry or formatting change, save the file again. Use File/Save, or click on the Save icon (the miniature diskette toward the left end of the toolbar).
You may have to reformat the cells, as the cell heights may be variable in the new spreadsheet thus created. Click on the row label for the top row (at the right side of the spreadsheet - e.g., "A" for the first row), then highlight all rows by dragging down the label column to the end of the entries. Go to Format/Row/Height, and enter the desired number - 12 or 14 is a good start. Similarly, the cell widths may need adjusting to accommodate the largest text line in each column. That is most easily done by placing the cursor over the boundary line between the column labels until you see a cross formed with arrows at each side, then clicking and dragging to the desired width. You may also need to change the coloring, shading, or borders of the spreadsheet cells. To do this, highlight the appropriate cell(s), go to Format, click on Cells, and then choose the appropriate formatting selection.