So Many Graphic Novels, So Much to Manage - Updates to My Inventory Spreadsheet

This is a supplemental entry to my series "So Many Graphic Novels, So Much to Manage - Inventory"
Part 1: https://steemit.com/computers/@phoenix32/so-many-graphic-novels-so-much-to-manage-inventory-part-1
Part 2: https://steemit.com/computers/@phoenix32/so-many-graphic-novels-so-much-to-manage-inventory-part-2
Part 3: https://steemit.com/computers/@phoenix32/so-many-graphic-novels-so-much-to-manage-inventory-part-3

I've had a couple of days to lay off the gas pedal a bit, even to kick it down from 5th gear to 4th. It's a nice feeling, and I decided to spend the last couple of days doing some refinements to my Inventory spreadsheet. I've been working with a particular focus on the "Author_Query" sheet, as mentioned specifically in "Part 3". I was vexed by the idea that while I could list all of the authors either by first name, last name, or a combination of the two, I was unable to utilize the COUNTIF function with any sort of success.

Also, on the "Character_Query" sheet, I realized that I made a wee bit of an error: I could search an entire array for a particular hero (i.e., Spider-Man, Wolverine, Batman, Batgirl, Robin, Scarlet Spider) or a particular secret identity (Peter Parker, Dick Grayson, Barbara Gordon, Stephanie Brown, Ben Reilly) and it would spit out the results that I needed. Yay! However... The problem was that I condensed the entire array of 7 columns down to 1, and that proved difficult with regards to certain types of queries. For instance, searching for "Batman" would usually produce a particular result of the hero as well as the man wearing the cowl - Bruce Wayne, Jean Paul Valley, Dick Grayson, Terry McGinnis, or even Damian Wayne. But what happens if there is a book in which the Mantle of the Bat is carried by more than one of them? I have one book in my collection that covers the different men who wear the cowl, with stories that span across the timeline of the DC Universe. Similarly, I have a Robin book which features all of the Robins up to that point, and so even if I worked the COUNTIF properly for that one column, it would not be an accurate count by any means.

The COUNTIF also cannot check partial contents. As I have the entries for each character entered into the "Graphic_Novels" sheet as {HERO_NAME}/{SECRET_IDENTITY}, that same data would appear in a single cell as it directly references the source data; in other words, I would have to separate the data into {HERO_NAME} and {SECRET_IDENTITY}, as that is the only way that the COUNTIF function could count the data when searching by either field.

But we'll get to that later.

The "Author_Quey" sheet

Unlike the the original "Character_Query" sheet, I was unable to get a proper output form a COUNTIF function. This was due to the absence of the "OK" column that I originally used in the "Character_Query" sheet; even though it worked well enough when searching for characters, I was not overly fond of it. I'm admittedly not 100% certain as to why I stopped liking it - only that I had no desire to eliminate it from the existing "Character_Query" sheet, as I felt that it was a done deal — which, of course, is not the case anymore.

This brought me to a sort of a crossroads, as I had originally used the "OK" column in the "Author_Query" sheet, but removed it when I decided to apply a Filter to the columns - this just as easily eliminated the (Blanks), or blank/empty cells, from the sheet without deleting them, and it meant that I did not have to have a column that simply kicked back one of two values: either "OK" or a blank cell. The downside to this was that I no longer had a stable counting method for the authors. So of course, rather than just restore the “OK” column, which would have been infinitely easier and far less stressful, I decided that a new formula was in order

which is then propagated down the entirety of both columns. That brought me to the COUNTIF and how I would apply it. When dealing with the two cells, E1 and G1, there are four possible generic combinations:

• (1) E1 is empty and G1 is empty → $E$1="", $G$1=""
• (2) E1 has a First Name and G1 is empty → $E$1<>"", $G$1=""
• (3) E1 is empty and G1 has a Last Name → $E$1="",$G$1<>""
• (4) E1 has a First Name and G1 has a Last Name → $E$1<>"",$G$1<>""

I had to add a pair of columns, which I placed to the very right of the sheet, and labeled them “AUTHOR_LEFT” and “AUTHOR_RIGHT”. Yes, I suppose I could have used “FIRST_NAME” and “LAST_NAME”, but I also wanted to emphasize the commands that I used in each of the columns:

• AUTHOR_LEFT:
• AUTHOR_RIGHT:

Both of these call upon the (potentially) entered AUTHOR_FIRSTNAME and AUTHOR_LASTNAME, respectively, and spit out a value if conditions #2-4 are met, and condition #1 would have no value, of course. Now, I could actually apply a COUNTIF based on all four conditions. Naturally, I used an IF function for it:

The cool part of this (relatively speaking, of course) is that part that reads E1&” “&G1. This is a neat little trick that is seldom used, probably as it is not that well known. Using the “&” connects strings of values together, and the “ ” is an actual space, with the quotation marks being the delimiter. For instance, if I wanted to insert a middle name or nickname, I would do something like this: E1&“ The Man ”&G1 — and in this instance, the only name for which I would want to search would be E1 = Stan and G1 = Lee, so the final output would be Stan The Man Lee. The application of this would be used on Column C, “AUTHOR”, as it would have the author’s first name and last name.

Otherwise, a search by Last Name would use a COUNTIF on column J, “AUTHOR_RIGHT” and a search by First Name would use a COUNTIF on column I, “AUTHOR_LEFT”. In the event of both E1 and G1 being blank, I made a change to the system. The previous version would simply populate all of the SERIES and TITLES from top to bottom, and leave the AUTHOR column blank. I didn’t really care for that, so I also made some changes to the SERIES and TITLES columns:

• SERIES:
• TITLE:

So that cleaned it all up and made it nice and pretty. And part of the cleanup was to “hide” Columns I and J, which is a neat little piece of the design where it squishes them together between surrounding columns/the end of the worksheet so they are out of sight, but they are not deleted so they are not out of mind. Maybe I am starting to gain a sense of the aesthetic after all…?

In summary:

1. Expanded the sheet by 2 columns, one for author first name and one for author last name
2. Changed the COUNTIF for the author count to account for a series of conditions, scanning one of three columns or leaving a blank
3. Reconfigured the sheet to kick out blank values in the cells in the event of both the first name and last name query boxes being empty

The “Character_Query” Sheet

Oh, this one went from bad to worse in terms of trying to find the right formula. As I stated previously, I had encountered the issue that there are times where both Peter Parker and Miles Morales appear in costume, or Bruce Wayne and Dick Grayson are in the same panel and both are wearing the cowl, or even the 90’s DC Comics event Zero Hour in which there are multiple Robins from different eras and Barbara Gordon as Oracle meets a time-displaced or timeline-displaced Barbara Gordon as Batgirl. That makes the whole “condensing down to one column” idea not worthwhile, no matter if I am searching by hero or by secret identity, as it will not display all of the results that I need.

This all calls back to the main sheet, “Graphic_Novels”, particularly the columns AR through AX. This array is what I called the Character_Tags and I would enter the data in as {HERO_NAME}/{SECRET_IDENTITY} in the case of those that have a dual identity. There are also a good number of characters who only have one name or another:

• The Joker (because who knows what his real name is, anyway?)
• Commissioner Gordon (although he was Batman for a spell, but that was only for a small run)
• Barbara Gordon (in “The Killing Joke”)
• Robin (in “Batman Year 100”)
• Dick Grayson (in the Grayson series)
• Bane (because he doesn’t have any other name)
• Daken (while he is also Dark Wolverine, but that was just a gig)
• Old Man Logan (because he stopped being Wolverine one fateful night)
• Kiden Nixon (from Marvel’s NYX series)
• Nick Fury (ya know, the Agent of SHIELD)
• Darth Vader (because the name “Anakin Skywalker” holds no meaning for him)
• Darth Maul (because he doesn’t have any other name)
• Luke Skywalker (because he also doesn’t have any other name)
• Kyle Katarn (because… oh, dang it, you get the idea…)

Not to mention teams and groups:

• Transformers (yes, collectively, as there are just way too many characters to list)
• X-Men Blue, Gold, or Red (yes, there were three teams going on, for about a 11 months…)
• We Are Robin (from the aftermath of one of the Joker’s assaults on Gotham City)
• USS Enterprise-A (Captain Kirk and crew)
• USS Enterprise-D (Captain Picard and crew)
• Voltron Force (to Sven or not to Sven, that is the question...)

Or even entire collectives:

• DC Universe (whatever state it is in now)
• Marvel Universe (ditto)
• Marvel Ultimate Universe (RIP 2011)
• DC’s New 52 (which lasted all of a hot minute)
• DC Rebirth (which I think they are over that now, but who can tell?)

Entries such as this created a whole ‘nother mess to sort out. What I invariably had to do was take the 7 columns and break them in half. Since the “/” is the separation point for the dual-identified characters, breaking each cell into two pieces would require a bit of finesse. I started out with the same conditions as I used in the “Author_Query” sheet, with the binary nature of cells B1 and D1 — either with a value or as an empty cell. However, I needed to add conditions from there.

The left side of the Character_Tag:

Left-Hand Value Case 1
If B1 had a value and D1 was empty, I also needed to analyze the contents of the cell to make sure that the left-hand part of the cell was the same as the entered value in B1, plus I needed to see if there was a “/” to separate a {HERO_NAME} from a {SECRET_IDENTITY}. If there is not, then the left-hand breakdown is to be blank; if there is a “/”, then it should have the left-hand value of the cell that matches exactly with the search key in cell B1.

Left-Hand Value Case 2
If B1 was empty and D1 had a value, again, the contents of the cell would have to be analyzed for matching length and value of said contents, and the presence or absence of the “/”. Again, the absence of the “/” gives a blank value, and the presence of the “/” will kick out a value that is the same length and identical value as the search key in B1. This part is slightly complicated, as it involves cutting off the right-hand portion of the contents of the cell which is the length of D1, and additionally cutting off one more place, which is the “/” itself, which you don’t want displayed. Not inherently difficult, but something of which I needed to be aware, since =B1 and =B1&“/” are two completely different strings.

Left-Hand Value Case 3
There are values in both B1 and D1, and together they are used to analyze the cell. Should the left-hand portion of the cell match B1 and the right-hand portion of the cell match D1, then the displayed value will be based on the length and value of B1; no need to involve D1 any further, so let’s give it a break, let it grab a candy bar and relax for a minute.

Left-Hand Value Case 4
Both B1 and D1 are empty. As there is no search parameter, this will kick out a blank cell. Nothing to see here, move along…

The right side of the Character_Tag:

Right-Hand Value Case 1
If B1 had a value and D1 was empty, this would end up similarly to “Left-Hand Value Case 2”, and checking for the presence or absence of the “/” would mean either: (1) cutting off the left-hand portion of the string and cutting off the “/” as well to display the right-hand portion of the cell, or (2) just displaying the cell itself if there is no “/”.

Right-Hand Value Case 2
If B1 is empty and D1 had a value, then this would be similar to “Left-Hand Value Case 1”. Checking for the “/”, the absence means taking the contents of the cell if it matches D1 in value and length, while the presence of the “/” means taking the value of the cell as it is.

Right-Hand Value Case 3
There are values in both B1 and D1, and just like in “Left-Hand Value Case 3”, both B1 and D1 are used to analyze the cell, and should there be a match, then the value that is kicked out is to match D1 in length and value; it is break time for cell B1 here, so give it a KitKat bar.

Right-Hand Value Case 4
Both B1 and D1 are empty. Same as “Left-Hand Value Case 4”. ‘Nuff said.

Finally, for both the Left-Hand and Right-Hand Values, I closed out the IF function with a blank value as well, since if there was something that did not fit into the parameters of Cases #1-4, which is really broken down into 6 pieces, well, it was just not something that I wanted to face, and it took me several days of plugging away at this to reach the point where I knew what conditions I needed to analyze for each cell, and then to chain and loop the functions together to construct the formulae for each cell.

And now for the COUNTIF…

This formula was substantially easier to construct, as it narrowed down the search parameters based on the values of B1 and D1. Everything was scanning the same array, H2:U. If either B1 or D1 had a value and the other was blank, the non-blank cell became the search key for the COUNTIF function, scanning the array and kicking out a number. If both B1 and D1 were blank, then it would read “[None Selected]” and the entirety of the arrays from F2:G and H2:U would remain blank. The “hard part” was if there was a value in both B1 and D1 — so I added a condition that the COUNTIF with B1 as the search key and the COUNTIF with D1 as the search key needed to equal each other, and then the COUNTIF would run based on B1 as the search key. Not that it would matter, really, but I chose B1 because I felt like it. No other reason. Lastly, if there was some instance in which the conditions were not met, then there would be a blank in place of a number.

Lastly was the aesthetic of the F2:G array. As there are now 14 cells that have character information in them, and filtering one of the fourteen could remove other information from view, and

Instead, the blanks can be filtered out using a filter on column F, “SERIES”, and that would present everything nicely. The formula for columns F and G are similar:

• Column F:
  
• Column G:
  

The OR function is exceptionally useful in this case, as it prevents having to parse out an IF function for all of these cases. That would read something like this:
=IF($H2<>"",Graphic_Novels!C2,IF($I2<>"",Graphic_Novels!C2,IF($J2<>"",Graphic_Novels!C2,IF($K2<>"",Graphic_Novels!C2,IF($L2<>"",Graphic_Novels!C2,IF($M2<>"",Graphic_Novels!C2,IF($N2<>"",Graphic_Novels!C2,IF($O2<>"",Graphic_Novels!C2,IF($P2<>"",Graphic_Novels!C2,IF($Q2<>"",Graphic_Novels!C2,IF($R2<>"",Graphic_Novels!C2,IF($S2<>"",Graphic_Novels!C2,IF($T2<>"",Graphic_Novels!C2,IF($U2<>"",Graphic_Novels!C2,""))))))))))))))

While that would certainly work, I found this to be far easier to manage:
=IF(OR($H2<>"",$I2<>"",$J2<>"",$K2<>"",$L2<>"",$M2<>"",$N2<>"",$O2<>"",$P2<>"",$Q2<>"",$R2<>"",$S2<>"",$T2<>"",$U2<>""),Graphic_Novels!C2,"")

I tend to space them into a more columnesque view for a few reasons, one of them being that it is easier for me to visually parse at a glance, and another being that, depending upon where the cell is located in the window, if will get bunched up and text-wrapped anyway, so I might as well have some control over that as I go, right?

But in all seriousness, the Inventory spreadsheet/database is a work in progress. It will never be finished in the strictest sense, because there is something that I can improve, something I can upgrade, or some formula that I can rewrite to make it better, more functional, or even faster. At the very least, I'll need to keep adding rows and propagating them with the proper formulae, as I now have exactly 1,200 graphic novels in my library as of my last purchase.

Here's a link to the upgraded spreadsheet template: https://drive.google.com/drive/folders/1jvkiIJoKVh5FY-as374LaLuzTgn4mze3?usp=sharing

As always, thanks for reading!