I'm posting this after reviewing the Meta Q&A, Non-code formula spreadsheet, and I believe I've got something here that meets the criteria there.

Edit: I've added documentation of the formulas below to make them easier to review.

Occasionally I find myself analyzing data that I need to turn into a spreadsheet with two levels of indices. For example, from the mock data:

Authors and their books
Sam Jones
    How to write comedy
    How to write mystery
Jim Smith
    How to avoid writing
    The pleasures of procrastination
    The book I didn't write
Bill Bailey
Sally Ames
    The art of sleeping

I want to generate the following spreadsheet:

sample 2

In words, I want to have one row for each title and I want an incrementing index for authors and another for each author's titles.

To do this, the first step is to turn the data into the following spreadsheet:

sample 1

Doing that is simple and straightforward, and I don't need to explain how I did that. The challenge is to use the data of sheet "sample 1" to generate sheet "sample 2". Obviously, it would be easy to manipulate these data manually into that form, but the challenge is when I have a data set with hundreds or thousands of rows in the structure of "sample 1", to convert it to the structure of "sample 2".

The formulas I have developed to do that are, in sheet "sample 2":

A5: =A4 + 1
B5: =IF(B4 = "", "", IF(ISTEXT(B4), 1, IF(H4 < G4, B4, IF(B4 = 'sample 1'!B$3, "", B4 + 1))))
C5: =IF(B5 = B4, "", B5)
D5: =IF(C5 = "", "", MATCH(C5, 'sample 1'!H$5:H$75, 0) + ROW('sample 1'!H$4))
E5: =IF($D5 = "", "", INDEX('sample 1'!B:B, $D5))
F5: =IF($D5 = "", "", INDEX('sample 1'!I:I, $D5))
G5: =IF(B5 = "", "", IF(F5 = "", G4, F5))
H5: =IF(B5 = "", "", IF(F5 = 0, "", IF(F5 = "", H4 + 1, 1)))
I5: =IF(H5 = "", "", INDEX('sample 1'!C:C, MATCH(1, INDEX((B5 = 'sample 1'!G:G) * (H5 = 'sample 1'!J:J), 0, 1), 0)))

That's the code I'd like reviewed. Is there a simpler or more efficient way to do this?

Documentation of what each formula does, to make them easier to review:

  • A5: Simple incrementing of a number for each data row.
  • B5: Author index:
    • Empty if the previous row's value is empty.
    • If the previous row's value is text, meaning that this is the first data row, then 1.
    • If the previous row's title index is less than the number of titles for this author, then repeat the previous row's value of the author index.
    • If the previous row's title index is equal to the number of titles for this author, then we have listed all the titles for that author. So:
    • If the previous row's author index equals the number of authors, then we're finished. Return empty.
    • Otherwise, increment the author index.
  • C5: Show the author index if it is different from that in the previous row.
  • D5: If the author index is shown in the previous column, then get the row number of that author index in sheet "sample 1".
  • E5: Author's name: If there's a value in the previous column, then get the author's name from sheet "sample 1".
  • F5: Number of titles: If there's a value in D5, then get the number of this author's titles from sheet "sample 1".
  • G5: Fill in this author's number of titles for rows where it's empty in the previous column. (This is used in the calculation of the next row's author index (B6).)
  • H5: Title index: If the number of titles is 0, then empty. Otherwise, if this is the first title for this author, then 1, otherwise increment from the previous row's value.
  • I5: Title: If there is a title index, then get the row number of the row in sheet "sample 1" with the same author index and title index, and then get the title from that row.

In order to understand the formula for I5, you also need one formula from sheet "sample 1" because its values are hidden by its narrow column width:

'sample 1'!G5: =N(G4) + IF(B5 = "", 0, 1)

Documentation of that: Column 'sample 1'!G:G provides the author index of each book.

  • \$\begingroup\$ No input after a month. I guess that at least means that no one found anything glaringly wrong with it. And no one has a simpler or more efficient way of doing it. \$\endgroup\$
    – NewSites
    Dec 2 '19 at 5:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.