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:
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:
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.