So Many Graphic Novels, So Much to Manage - Inventory Part 3

Continuing from this post: https://steemit.com/computers/@phoenix32/so-many-graphic-novels-so-much-to-manage-inventory-part-2

Part 2 covered conditional formatting and data sorting for my collection based on Publisher. Part 3 is going to cover the numerals and the Query sheets. So let's go!

Just to reiterate, the breakdown of the columns on the "Graphic_Novels" sheet is as follows:

• A - BookID
• B - Series_Name
• C - Title
• D - Publisher
• E - Type
• F - ISBN
• G-U - Issues
• V-AO - Authors
• AP - Shelf #
• AQ - Status
• AR-AX - Character_Tags

Most of what we'll be looking at lives in the arrays of Columns G-U (Issues), Columns V-AO (Authors), and Columns AR-AX (Character_Tags). These are intensive arrays, as the data they contain is some of the most precious in the whole database. I created 3 different Query sheets: Character, Issues, and Author. I also decided that the numerals were effective for other reasons, and I wanted to see them decked out, so I'm going to start there on the "Counts" sheet.

This is not the first set of columns in the "Counts" sheet, but it is probably the easiest to breakdown, allowing us to ease into the pool instead of diving right on in. Column D is an ID#, Column E is the "Series_List", and Column F is the Count for the Series. Everything hinges on Column E and how it is populated. Column E, in turn, is dependent upon the "Graphic_Novels" sheet. Again, using Row 1 as a header row, I start parsing the information in Row 2, specifically cell E2. I use this formula: =UNIQUE(Graphic_Novels!B2:B). I covered that formula in the last post, but I will state again that it does not display the information in alphabetical order by default, but in the order in which the items first appear in the given list; in this case, the source data is already sorted alphabetically, but if I was to re-organize the information in the "Graphic_Novels" sheet by BookID or Publisher or ISBN or something else entirely, then it would display the Series_List in that sequence instead. Also, "UNIQUE" will maintain the integrity of the data set - if it is a column, then it will utilize a column; if the data is in an array, it will utilize an array.

Cell D2 contains a special formula that is dependent upon its neighbor cell E2: =IF(E2="","",1). Cell D3 is also dependent upon its neighbor to the right, however, it derives its value based on the cell above it: =IF(E3="","",D2+1). While I suppose I could have just said =IF(E3="","",2), that would mean editing the cells and retyping the formula the entire way down, as opposed to copy-and-paste, which changes the E3 to E4, then E5, et al, as well as D2 to D3, then D4, et al. The +1 aspect means that I will have the numerical continuity that I was looking for as well. This is used for conditional formatting on the "Publisher_Breakdown" sheet in cell C2: =MAX(Counts!D2:D).

Column F is for the counts of each Series. Cell F2, for instance: =IF(E2="","",COUNTIF(Graphic_Novels!B2:B,E2)). The crux of it is not the "IF" function, which, as you saw, is in front of everything. The idea of =IF(E2="","",{VALUE}) is so that in the absence of {VALUE}, whatever that may be, there will not be an unsightly error code or "N/A" value that could be mistaken for a data entry of some variety. Basically, the "IF" part of =IF(E2="","",{VALUE}) is a blanking panel, if you will. The function that occupies our attention for the moment is "COUNTIF". "COUNTIF" requires two pieces of information - the first being a column or array to search, and the second being the search key. If I was to search for a static value, say something like =COUNTIF(Graphic_Novels!B2:B,"Superman"), it would find all of the "Superman" values - although it would ignore things like "Superman/Batman", or "Superman: The Man of Steel", or "Superman in Action Comics". Also, since I wanted the numbers in cell F2 to reflect the number of times that the name of the series in cell E2, which is subject to change based on the sorting of the "Graphic_Novels" sheet, I made cell E2 the {VALUE} in the "COUNTIF" formula in cell F2. Naturally, the search key changes from E2 to E3 to E4, et cetera as the formula is copied down to the next cells. I populated the entire column F with this formula. "COUNTIF" will play a massive part of the next two data sortings as well.

Let's talk about Authors. As I have the potential for entering 20 authors per book, the array could, theoretically, hold 23,700 different entries for authors - that is 20 authors times 1185 different books (presuming each book has 20 authors, which, of course, is not the case). I can guarantee that there will be duplicate entries, even without having 23,700 entries for authors in the array, so "UNIQUE" will need to be included in the command somehow as well.

Columns H and J are echos of Columns D and F, so I won't rehash them, especially so soon after walking you through it; I mean, you can scroll up a bit, right?
The fun and games take place in Column I. I left it all blank, as it would automatically populate due to the formula in cell I2:
=UNIQUE(TRANSPOSE((SPLIT(TEXTJOIN(",",TRUE,{Graphic_Novels!V2:AO}),",",TRUE,TRUE))))
Well, that is one ugly mess. So let's first talk about what I wanted to do, and then break down this formula as to how I achieved the result that I wanted. I had all of the Authors spanning an array on the "Graphic_Novels" sheet, form columns V to AO. The array itself would read as V2:AO, meaning that I was starting in cell V2 and going over to column AO, and the lack of number in addition to "AO" meant that I was not delimiting the depths of the rows. Had it been labeled as V2:AO11, it would mean an array that is 20 columns by 10 rows. The open-ended nature of V2:AO means that I can keep adding rows as I collect more books. Now, what I wanted to do was convert the entire array of V2:AO into a single column. Yes, I wanted to condense a 20-column/infinite row array down to a single column. And then I wanted to be picky and not have the values repeated, which really is the capstone to the whole thing here.

"TEXTJOIN" is a function that takes a series of data in an array and links all of them together - into a single cell. =TEXTJOIN(",",TRUE,{Graphic_Novels!AR2:AX}) reads as follows inside of the function:

• "," is the delimiter, the thing that is used to separate each value - I used a comma, although any symbol could be used.
• TRUE tell us to ignore blank spaces; putting FALSE instead would have blank spaces included
• {Graphic_Novels!AR2:AX} is the array from which the information is taken

The next outward layer is "SPLIT", which will separate that which has just been joined by the "TEXTJOIN" into individual cells: SPLIT(TEXTJOIN(",",TRUE,{Graphic_Novels!AR2:AX}),",",TRUE,TRUE)

• In place of a normal series of words or characters, the "TEXTJOIN" is used in here, but any text can be used in here (i.e., First,Second,Third,Fourth,Fifth)
• The delimiter, which you can set as anything, really - just make sure it is a character that is not used in the names of the Series of the books
• The first TRUE determines whether or not to divide text around each character contained in delimiter - helpful if you have a Series that is a single letter or symbol
• The second TRUE makes the "SPLIT" function remove empty text messages from the split results - just another way to ensure that there are no blanks. I don't like those blanks, as they tend to get in the way of things

The last formal section is the "TRANSPOSE" function. Simply, "TRANSPOSE" flips the numerical values of the array in question. So using the example from above of V2:AO11, this is a 10 row x 20 column array. Applying =TRANSPOSE(V2:AO11) would turn it into a 20 row x 10 column array, and it would place the values in them accordingly. Cell Z15, for instance, is in the 15th row and the 5th column, but "TRANSPOSE" turns it into the 5th row and the 15th column. Pretty nifty, I must say. In the case of the array V2:AO that has been processed through the "TEXTJOIN" and then "SPLIT" into individual cells in a single row, the 1 row becomes 1 column.

Lastly, the "UNIQUE" function is once again applied, removing repeat authors. With 1185 graphic novels in my Inventory, there are 2,203 entries for authors. The "COUNTIF" function in column J2:J shows us that there are TONS of multiple entries for each author.

The same function is applied to the Character_List in Column A of the "Counts" sheet, and again the "COUNTIF" function gives the totals for each character. No need to rehash that which I just broke down, right? I did run into a minor issue with this, because I would really like to see how many books Dick Grayson is in, no matter if he is Robin, Nightwing, Batman, or himself undercover with Spiral. It is rather difficult to separate out without doing separate cells in the original array for both hero name and secret identity.

This seems like a great time to talk about the Query sheets, starting specifically with the "Character_Query" sheet. Columns A, B, C, and D are all used for listing particular values.

• Column A - the "OK" column, using the formula: =IF(AND(left(D2,len($F$1))=$F$1,right(D2,len($H$1))=$H$1),"OK","")
• Column B - the Series name column, using the formula: =IF(AND(left(D2,len($F$1))=$F$1,right(D2,len($H$1))=$H$1),Graphic_Novels!B2,"")
• Column C - the book Title column, using the formula: =IF(AND(left(D2,len($F$1))=$F$1,right(D2,len($H$1))=$H$1),Graphic_Novels!C2,"")
• Column D - the Character column, which uses a rather complex formula that I will cover below.

Other cells in Row 1 are vital and are referenced in a static way, using the "$" so that they are not changed as the formula are copy-and-pasted to other cells. Cell E1 is a label for cell $F$1, where E1 ="CHARACTER_NAME:". Likewise is G1 a label for $H$1, where G1 = "SECRET IDENTITY:" Cell I1 is different from the rest of the whole page, as it spits out a single value based on column A2:A - I1 =COUNTIF(A2:A,"OK"). Unlike previous applications of the "COUNTIF" function, I am interested in the static value of "OK" and nothing else to achieve the numerical value in cell I1.

Now, let's look at the formula that appears in the cells in column D:

This is a gigantic "IF/THEN" statement, and it basically scans the entire array by row. So cell D2 is concerned simply with row AR2:AX2, cell D3 is concerned with row AR3:AX3, and so on. I'll break it down bit-by-bit here:

• =IF(AND(left(Graphic_Novels!AR2,LEN($F$1))=$F$1,right(Graphic_Novels!AR2,LEN($H$1))=$H$1),Graphic_Novels!AR2,
  The "AND" function joins together the two conditions based on the values of $F$1 and $H$1
  The "LEFT" function looks at left portion of a cell, and requires a delimiter of the length for which it is scanning, from left to right.
  The "RIGHT" function does the same thing, only from - you guessed it - starting at the right and reading to the left.
  Both "LEFT" and "RIGHT" require a measurement. If you are using static values, then plugging in any number would suffice, and the formula would look something akin to this: =LEFT(A110,3), and the output from that would be the left 3 characters of cell A110. "LEN" function can be used with a constant value: =LEN("phoenix32"), and the output is 9. So my username is nine characters long. Since my system is dependent upon cells whose contents are variable, $F$1 and $H$1, then the mutable lengths of the text in those cells comes into play.
  So LEFT(Graphic_Novels!AR2,LEN($F$1))=$F$1 is part of the condition that must be met for the "AND" function.
  Also, RIGHT(Graphic_Novels!AR2,LEN($H$1)=$H$1 is the second condition that must be met for the "AND" function.
  The "IF" function says that, should the "AND" be true, then the value to be displayed will be that in cell Graphic_Novels!AR2.
  Else...
• IF(AND(left(Graphic_Novels!AS2,len($F$1))=$F$1,right(Graphic_Novels!AS2,len($H$1))=$H$1),Graphic_Novels!AS2,
  Same thing, although applied to cell Graphic_Novels!AS2.
• IF(AND(left(Graphic_Novels!AT2,len($F$1))=$F$1,right(Graphic_Novels!AT2,len($H$1))=$H$1),Graphic_Novels!AT2,
  Same thing, although applied to cell Graphic_Novels!AT2.
• IF(AND(left(Graphic_Novels!AU2,len($F$1))=$F$1,right(Graphic_Novels!AU2,len($H$1))=$H$1),Graphic_Novels!AU2,
  Same thing, although applied to cell Graphic_Novels!AU2.
• IF(AND(left(Graphic_Novels!AV2,len($F$1))=$F$1,right(Graphic_Novels!AV2,len($H$1))=$H$1),Graphic_Novels!AV2,
  Same thing, although applied to cell Graphic_Novels!AV2.
• IF(AND(left(Graphic_Novels!AW2,len($F$1))=$F$1,right(Graphic_Novels!AW2,len($H$1))=$H$1),Graphic_Novels!AW2,
  Same thing, although applied to cell Graphic_Novels!AW2.
• IF(AND(left(Graphic_Novels!AX2,len($F$1))=$F$1,right(Graphic_Novels!AX2,len($H$1))=$H$1),Graphic_Novels!AX2,
  Same thing, although applied to cell Graphic_Novels!AX2.
• "")))))))
  *This is the last "Else" part of the function - in the event that none of the other options are true/all of the other options are false, then this cell will display a blank.

Working from right to left, let's go to cell C2:

Rather than repeating the full formula of D2, instead I just reference cell D2 in the "LEFT" and "RIGHT" functions that are joined within the "AND" function. Should the "AND" be true, then the output would be the value of cell Graphic_Novels!C2, which is the Title of the book, otherwise it would be left blank.

Cell B2 does the same thing, calling to the Series name, and outputting the value of cell Graphic_Novels!B2 or remaining blank based on the "AND" function.

Cell A2 will either be blank or say "OK", again based on the value of cell D2. =IF(AND(left(D2,len($F$1))=$F$1,right(D2,len($H$1))=$H$1),"OK","")
This is something that I would like to tweak one day, maybe parse down the mechanics of this a little bit. Currently, this will allow for a search for hero name, secret identity, or both. This is vital, as there are heroes who have passed on their mantles to others, and secret identities that have changed hero names. Some examples:

• Batman: Bruce Wayne, Jean Paul Valley, Dick Grayson, Tim Drake, Terry McGinnis
• Spider-Man: Peter Parker, Ben Reilly, Miles Morales, Otto Octavius
• Jason Todd: Robin, the Red Hood
• Eddie Brock: Venom, Anti-Venom, Toxin

Cell I1 gives a count and lets me confirm the character count for which I am looking. If I am looking for a combination, such as Batman/Bruce Wayne, and the output matches the "COUNTIF" from the "Counts" sheet in column B, then I know that I have matching stuff. But then again, since I cannot get separate counts for Bruce Wayne alone, this query will give me a count of how many books he appears in, either as Batman, as Terry McGinnis's mentor in Batman Beyond, or even if he appears out of the Batsuit, such as during the "Knightfall" arc.

The results of this query can be filtered using header row filters. This breakdown shows all of the Bruce Wayne entries, regardless as to whether or not he is Batman or he is "so debonair, so dapper..."

Looking at the filter, you can see that there are definitively two different entries for Bruce Wayne:

I know that, out of 1185 graphic novels, there are 170 that are tagged with Batman/Bruce Wayne, and 12 that are tagged simply with Bruce Wayne, which matches up nicely with the total of 182 that the "COUNTIF" provided for us.
Here's a sample of the output of the this query for Mr. Wayne:

Let's hop over to the "Author_Query" sheet, which functions on the same principle. Instead of Hero and Secret Identity, the search is conducted by FirstName and LastName. The search utilizes the same functions, and I did refine things a wee bit by eliminating the "OK" column, and instead filtering out the blanks from the author list. The author list, by the way, is generated with the same type of formula as found in column D in the "Character_Query" sheet.

As you can see, that is monstrous, almost 3 times bigger than its counterpart in column D of the "Character_Query" sheet. The beauty of this is that the whole of the setup is that no matter how many times an author appears in the array of Graphic_Novels!V2:AO, the same author will not appear more than once in the same row. So I was able to condense things down to a single column without using the "TRANSPOSE" function - which is great, because I only learned that recently. Besides, I want to condense based on whether or not the author's name appears in a particular row, or else leave that row blank.

The choice to search by both first name and last name is based on the idea that I would routinely forget the particular spellings of certain names, such as Transformers author Mairghread Scott. Honestly, I love her name, but for the life of me, I cannot spell it - but her surname of "Scott" is pretty easy,

and it gives me the results I am seeking:

Now we reach the "Issues Query" sheet. This was the start of the queries, and I constructed this originally to help me keep track of the issues of Nightwing (Volume 2), as I was attempting to collect the whole set. There were issues of Nightwing (Volume 2) that appeared, of course, in the original title, as well as in various Batman (Volume 1) story arcs - No Man's Land, Bruce Wayne: Murderer?/Fugitive, War Games - and other titles, such as Birds of Prey (Volume 1) and compilations, such as Robin: The Teen Wonder (ISBN: 9781401222550). I also know that there were a few stray issues that did not get published again in any graphic novel, so I was on the lookout for those as well. This became an echoing of the full array from the "Graphic_Novels" sheet: columns G-U on "Graphic_Novels" were made visible on columns F-T on "Issues_Query". All static values, F2, for instance, reads as follows: =IF(LEFT(Graphic_Novels!G2,$B$1)=$A$1,Graphic_Novels!G2,""). The rest of the array reads in the same way, with "G2" being replaced with the respective match to the new cell but $B$1 and $A$1 always being maintained and static.

Column E is dependent upon column D, so we'll skip over to column D:
=IF(OR($F2<>"",$G2<>"",$H2<>"",$I2<>"",$J2<>"",$K2<>"",$L2<>"",$M2<>"",$N2<>"",$O2<>"",$P2<>"",$Q2<>"",$R2<>"",$S2<>"",$T2<>""),Graphic_Novels!B2,"")
The "OR" function ensures that only one of the values needs to be true in the row in order for the Series name to appear, else the value in that particular row of column D will be left blank.

Column D then branches out for columns C and E. Column C is a simple operator: =IF(D2="","","OK"). Column E is barely more complicated than that: =IF(D2="","",Graphic_Novels!C2). Like I said, barely.

South of row 1280 is where things get a bit different. I wanted to have both graphic novels and comic books on a single sheet in terms of the query, and it does tend to work out pretty nicely. Granted, once your collection exceeds 1270 graphic novels, you're going to have to insert some rows and copy-and-paste some code from row-to-row in order to match up with the rows on the "Graphic_Novels" sheet. As for the comic books, the formulae live in columns C-F. The good news is that the formula in column C is identical throughout the sheet; column E looks like this: =IF(D1281<>"",Comic_Books!C2,""); column F looks like this: =IF(D1281<>"",Comic_Books!F2,""). All 3 of these columns rely on the values in column D, which has this formula: =IF(LEFT(Comic_Books!B2,$B$1)=$A$1,Comic_Books!B2,""). This is identical in design to the formulae in columns F-T, north of row 1280, only citing the "Comic_Books" sheet instead of the "Graphic_Novels" sheet - truly not that big of a difference.

Well, that about does it for my Inventory spreadsheet. I have a great interest of doing things like this, and the more I have done, the more I want to do with it. I love how the numbers produce a lovely visual as a graph, how the data can be seen and analyzed, and how it tells a story about the whole of it - how my collection started, how it grew, what was released or re-released and when, and how I've built my collection into something that I can look on with pride, and maybe leave to any of the nieces and nephews that are in my life.