Jump to content

Please read the Forum Rules before posting.

Photo

Sorting Scripture references in Excel


  • Please log in to reply
13 replies to this topic

#11 JimSussex

JimSussex

    New to Bible Support

  • Members
  • Pip
  • 1 posts
Offline

Posted 01 September 2017 - 06:56 AM

You can sort in bible book order using the VLOOKUP function in Excel. Create a table  with a column of numbers 1 through 66. In the column to the right list the books of the Bible in order. So now each book is assigned it's number.  This is the lookup table. 

Add the table into the worksheet that you want to sort.  Now in the first cell of the column immediately to the left of the column with the unsorted book names insert the formula  =VLOOKUP(C1,$A$104:$B$169,2,FALSE), where in this example C1 is the first cell to the right of the formula with the out of order book name. the $A$104:$B$169 is the location of the table,  the "2" is the column in the table that has the corresponding book number( i.e. Genesis is #1 Revelation is #66), the FALSE argument is needed so excel will look at each cell in the way needed.

Now fill down the column so the formula is in each cell if the column. Then do a copy /paste to replace the formulas with their actual values.

You can now remove the table and format the worksheet to sort.



#12 BH.

BH.

    Utility Developer

  • Contributors
  • PipPipPipPipPip
  • 1,173 posts
  • LocationCalifornia
Online

Posted 01 September 2017 - 07:43 PM

Image already added
 
 

Both e-Sword TooltipTool NT and e-Sword HotKey have scripture sorting functions for scripture lists.  It is not excel. 



#13 Chica

Chica

    Liking e-Sword

  • Members
  • PipPip
  • 18 posts
  • LocationCalifornia
Offline

Posted 21 April 2018 - 11:42 AM

Here is a quick something I put together.
An Excel Spreed Sheet to help find Book|Chapter|Verse numbers & corresponding verse number in the whole Bible.
 
Nothing fancy.
Part came from a theWORD resource (The B|C|V Column).
Part came from the Web page I previously linked to. (The Number<>Book Chart)
And the verse number in the Bible column just came from an equation.
 
Maybe this will benefit someone.
 
Roy
 
(Corrected alignment issue on spreedsheet and new screen shot)

--Looks interesting but the link no longer works, can you repost?

#14 Chica

Chica

    Liking e-Sword

  • Members
  • PipPip
  • 18 posts
  • LocationCalifornia
Offline

Posted 21 April 2018 - 11:45 AM

You can sort in bible book order using the VLOOKUP function in Excel. Create a table  with a column of numbers 1 through 66. In the column to the right list the books of the Bible in order. So now each book is assigned it's number.  This is the lookup table. 
Add the table into the worksheet that you want to sort.  Now in the first cell of the column immediately to the left of the column with the unsorted book names insert the formula  =VLOOKUP(C1,$A$104:$B$169,2,FALSE), where in this example C1 is the first cell to the right of the formula with the out of order book name. the $A$104:$B$169 is the location of the table,  the "2" is the column in the table that has the corresponding book number( i.e. Genesis is #1 Revelation is #66), the FALSE argument is needed so excel will look at each cell in the way needed.
Now fill down the column so the formula is in each cell if the column. Then do a copy /paste to replace the formulas with their actual values.
You can now remove the table and format the worksheet to sort.

This did not work for me, thankfully I used to use look-ups for work, I finally got it to work but the lookup page has to have bible names first and be sorted alphabetically & formula looks like this:

=VLOOKUP(B2,Sheet2!A:B,2,FALSE)

Edited by Chica, 21 April 2018 - 11:46 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users




Similar Topics



Latest Blogs