Fill cells with next chapter based on a new sequence of numbers in another column.

  • The problem: I have a file which has Name of the book (Column A), Chapter number (Column B), colon (Column C). verse number (Column D) and the text (Column E). All of these are then combined in Column F for the final results. The problem is inserting the chapter numbers.
    Possibility one: Most of the time I have the beginning chapter number which appears in Column D, before the next chapter. If it is possible to read the new number in Column D and fill in the following cells in Column B until the next change of number in Column D.
    Possibility two: Read the change in verse number from Column E when it starts over at 1 and change the chapter number up by 1 filling in the cells of Column B with that new number until the next number 1 comes up in Column D.


    Thank you.

  • Hello,


    For a fully customized solution ... you should not hesitate to attach your file ... with your next message ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello again,


    In cell F1 ... you have following formula :


    Code
    =A1&" "&B1&C1&D1&" "&E1


    and then you copy this formula all the way down ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim, thank you so much for being willing to help. Unfortunately, I think what you are suggesting is what I already have. What I am looking for is a way to fill in Column B, based on the information found in Column D. In the sample I simply filled them in manually, but as there are hundreds of chapters, this is a bit laborious.

  • Hello again,


    Sorry did not read your initial message carefully enough ...:-(


    Attached is your test file ... with a couple of formulas to speed up your work ... (columns B and D)


    Hope this will help

  • Glad it is sorted out ...:wink:


    Thanks for your Thanks ...AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!