Interesting Challenge: Automatically Pulling in Legal Information into a Readable Document

This week I got a new interesting challenge. My HR VP needs to have an easier way of updating HR legal information that comes in about two times a year. She either receives or downloads the information, of which there are maybe about 12 separate pieced of information.  I received 4 as examples. Since the company has grown and now operates in multiple states, there is a greater need of finding a better way to collate the information by state. She had tried getting someone to put the information together by state in an Excel format but that someone (probably her company that she works for) didn't do it.

I looked at the examples and I thought, "Oh, this should be relatively easy. It looks like a bunch of VLOOKUPS or INDEX/MATCH formulas."

Not.

Each of those 4 examples had unique issues that required some creative problem. What I thought would be a fun and creative exercise of creating an automated tool with a legible/design for conveying information turned out to be way more. It is still fun and creative but it is also a really deep puzzle (at least to me). And I'm still trying to create a design that helps pull in information and be easily read. 

Here are some of the interesting puzzles I encountered:

1. In the minimum wage information, you can't just do a simple VLOOKUP by state. A state may have multiple "categories": while Arizona could be looked up as Arizona, California had three categories - California, California - small employers (less than 26), California - large employers - greater than or equal to 26. So extra work needed to be done to pull in the California data.
2. Because some states could have multiple categories (I think one state had 7), you could not expect one line of information; it could be up to 7 lines of information.
3. Also within the minimum wage information, for legibility purposes, a cell could be shown with multiple rows within a cell but a VLOOKUP (once you solve for issue #1) would pull in one long row of text - but you really need it to be broken up for presentation purposes. By the way, the way to show multiple rows in a cell or to apply row breaks in the cell, you use keystrokes ALT-Enter.
4. Once you passed all of those puzzles, the outside of work legal information threw up new puzzles. Instead of information for each state, or state category, coming in one row, a state could have two rows of information. In other words, the state could be two merged cells merged but the other information comes in two or more rows.
5. Then there is the converse: a state may come in as multiple categories but share the same information. State California might come in as three rows (due to different circumstances) but the legal requirement for each of the circumstances are the same so the legal information comes into one merged cell (3 cells merged into one).
6. There is also the puzzle of varying row heights. Since these legal documents will be printed by state in PDF format, we would like use space efficiently. We want the row to be just the right height - not too small that some text are cut off and not too large that you have too much white space.

I'm not done yet with setting up the automation but it looks like so far that the puzzles center on merged cells and varying row heights. I'm pulling in new ways of doing things: using unusual array formulas and an unusual use of get.cell.

And, I'm still working on the budget. I ran into some more problems, but that is for a later post.