Data Formatting Example: Generating a Randomized List of Bible Chapters
Problem Statement
Generate a complete, randomly shuffled list of all 1,189 chapters of the New American Standard Bible.Summary
"Why would anyone want this?", you may ask. The random Bible chapter is my preferred choice for a daily devotional. Many devotionals are reading plans that will take you through the whole Bible (or a major segment of the Bible) in a period of time, like a year. In order to read the Bible in year, you would be reading three or more chapters at a sitting. That can be hard when you are in the middle of, say, Leviticus. To their credit, the reading plans select those three chapters from different parts of the Bible. I have used plans like these before, but spread over three to five years rather than just one year.The one thing that can make these reading plans better is if each day is a surprise. It's kind of like putting your music player on shuffle. We can use technology to make this work! My phone has a check list management app called Listomni. Listomni can import list items in CSV format. Once I have the shuffled list of Bible chapters, I email it as an attachment to my phone and import it into the Bible Reading list that I have set up in Listomni. Thereafter, every day that I complete a reading, I check it off on the list. Listomni keeps track of the next reading up on the list. This article describes a simple way to generate the randomly shuffled list using a spreadsheet program like Excel or OfficeLibre Calc. It requires an iterative formula that can count its way through each chapter number. It also requires use of the spreadsheet rand() function and sorting to shuffle the results.
| The data resource that we need is a table of the 66 books of the Bible and the number of chapters in each book. One of those is available here. I copied and pasted the tables from this source deleting the extra headings to get the simple table at the right. It lists Genesis with 50 chapters in row one, Exodus with 40 chapters on row 2 and so on. This is the index tab of the spreadsheet document. The chapter counter formula will refer to this table to know when to reset to one starting new chapter. |
|
The chapter_list tab contains the book and chapter counters. Column A is the book counter. Column B is the chapter within the book. They are initialzed at row 2 to A=1, B=1, which is Genesis 1.
After row 2, the formula for book counter, in pseudo-code, is
An = IF(Bn-1 < INDEX(index.$B$1:$B$66,An-1,1),An-1 ,An-1 + 1)
If the chapter counter in the previous row has not reached the last chapter of the book, the book counter remains the same as the previous row. Otherwise, the book counter is incremented to the next book.
Likewise, the chapter counter changes depending on whether the book has changed:
Bn = IF(An-1 = An, Bn-1 + 1, 1)
The formula in column C concatenates the name of the book and chapter number to make the full chapter name that will go on the shuffled list. The column C formula also indexes into the list of book names in the index tab to get the name of the book.
Column D contains a reference to the rand() function, which populates the column with a pseudo-random number. The random number provides the mechanism for random shuffling.
| In the final step, I manually copy columns C and D over to the random_list tab, making sure to use the "Paste Special" menu item to paste the clipboard contents as values rather than as formulas. This captures both the chapter list and random numbers as plain data. Now the chapter list is in column A and the random numbers are in column B of the random_list tab. Sort the rows in this tab using column B as the sort key. This shuffles the rows into random order. Column A now has the randomly shuffled list of Bible chapters. It is ready to be exported in a CSV file. |
|