Excel Hole Finder

[Fred Slota]

https://docs.google.com/spreadsheets/d/1EHx0jn5wJdyi_nZsGX-IMiwRA-fYkVWf/edit?usp=sharing&ouid=104057741940766877668&rtpof=true&sd=true

 

A work of many, many...  many hours...   soooooo many hours

All formulas, no macros.  Uses Spill formulas, so might not work on sufficiently older versions of Excel.

 

Finds holes of unowned issues from 1-10 issues long in owned titles, where "owned issue" is configurable between Prime Var or Any Var, First Print or Any Print. (Prime Var is regular issue, or VarA if no regular issue).  Works separately on normal issues, Anl, Bk, etc.

Also finds missing issues in near complete titles, those titles missing up to n or at least n% issues (normal, Anl, Bk. etc.).

Also also finds fractional issues between or adjacent to owned issues.

 

Has several other reports.

 

ComicBase            
    Report | Item Checklist        
        Series to Print | Selected Series | Choose…    
            Series in Stock
            Select all Series
            OK
        Report Items    
            Check Items in my collection
            Check Missing Items
        Cover Size    
            None
        Uncheck everything else    
        Preview    
            Export Report
            File Type .CSV
            Save as "Item Checklist.csv" in the same folder as this spreadsheet
        Close    
Excel            
    Data        
        Queries & Connections | Refresh All    
    Set Options on the Options Tab        
    F9 to Recalculate        

 

Uses a data connection to the saved .csv file - best way to avoid 1-2 -> Jan 2 and other Excel import shenanigans.  I'm not sure how automatically this will work for others with directories for saving data; whether Excel will see the file in the local directory, or if you'll have to reconnect the file.

Takes a good minute or so to open.  Takes a good minute or so for it to churn through my data.  I tend to disable the automatic calculations and use manual F9 to recalc the sheets.  (Excel | File | Options | Formulas | Calculation Options), but I've saved it with Automatic turned on.  It used to take a good 30 minutes to an hour to process.  Spent a lot of time tweaking for efficiency, adding subsets that were sorted so XLOOKUPs could operate efficiently, etc.  Uses a lot of named ranges to improve readability and understanding of the process.

 

Feedback appreciated.

 

Enjoy.

[Steven L. Dasinger]

Downloaded and followed steps.
Encounter the following problems.
When click on Refresh, Error Message:
Initialization of the data source failed...

A) Can you supply the steps/options to define a new Connection?
OR
B) It looks like Tab Item Checklist is a list of your data.
Can I replace yours with copy/Paste?

 

[Fred Slota]

I worked with the data connection because, once connected, there were no data formats needed - no numbers interpreted as dates, I forget what other weird things happened.   Copy/Paste did some odd things, File import did some odd things.  Making the data connection to the .csv file, once made, just worked.

 

Give me a few minutes to test out reconnecting to a new file.

[Fred Slota]

And that's why we test with other people...

I need to add an insulating layer in between the data connection and the formulas that are accessing that page.

 

I can replace the .csv file and just click Refresh.  It looks like, as part of the process for other people to make a new connection, they need to delete or rename tabs, and that permanently breaks the formulas that currently directly point at the "Item Checklist" Tab.

 

 

 

[Fred Slota]

New link.  version 3.01

https://docs.google.com/spreadsheets/d/15VtjjqVB_s8ihonYakPgrh0IahmhB5m3/edit?usp=sharing&ouid=104057741940766877668&rtpof=true&sd=true

 

First Time use instructions

Excel            
    Data        
        Queries & Connections    
            Right Click on Item Checklist in the side panel and choose Delete
            Confirm Delete in Popup             
            Close Queries & Connections side panel
    Delete the "Item Checklist" tab
    Data        
        From Text/CSV    
            Select "Item Checklist.csv" and click "Import"
            Load
        Close Queries & Connections side panel
    F9 to Recalculate

 

After this, the normal instructions of simply needing to refresh the connection will re-read from the .csv file.

 

Looks like I only had to rewrite about 5 formulas, to protect the formulas in strings and INDIRECT() them so they don't break when a new user makes a new association.

Edited February 23, 2023 by Fred Slota
One wrong step

[Fred Slota]

My .csv, which includes all owned and unowned issues of owned titles has about 120,000 entries.

I think with v3.01, it supports a .csv of up to 500,000 entries.

[Fred Slota]

No hurry, but did you manage to get it working?

[Steven L. Dasinger]

Sorry. Been busy doing other things. I will try to get to in very soon.

[Fred Slota]

Again, no hurry...  just wondered with the fast feedback earlier whether there was any new news...

[Steven L. Dasinger]

Using Excel 2013, the instructions don't match exactly.

What I can follow got a connection and the data imported.

After that, refresh / recalculate does nothing and there are a lot of broken links.

Hopefully, someone with a new version can get this to work.

[Fred Slota]

Apologies, I hadn't looked it up because I hadn't considered that it wasn't widely available.  Apparently, the spill formulas I used are available only in Excel 365 on the various platforms.  I knew they were new, but hadn't considered how new...

 

Thank you for trying.