Fred Slota Posted February 23, 2023 Share Posted February 23, 2023 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. Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted February 23, 2023 Share Posted February 23, 2023 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? Link to comment Share on other sites More sharing options...
Fred Slota Posted February 23, 2023 Author Share Posted February 23, 2023 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. Link to comment Share on other sites More sharing options...
Fred Slota Posted February 23, 2023 Author Share Posted February 23, 2023 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. Link to comment Share on other sites More sharing options...
Fred Slota Posted February 23, 2023 Author Share Posted February 23, 2023 (edited) 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 Link to comment Share on other sites More sharing options...
Fred Slota Posted February 23, 2023 Author Share Posted February 23, 2023 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. Link to comment Share on other sites More sharing options...
Fred Slota Posted February 27, 2023 Author Share Posted February 27, 2023 No hurry, but did you manage to get it working? Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted February 27, 2023 Share Posted February 27, 2023 Sorry. Been busy doing other things. I will try to get to in very soon. Link to comment Share on other sites More sharing options...
Fred Slota Posted February 27, 2023 Author Share Posted February 27, 2023 Again, no hurry... just wondered with the fast feedback earlier whether there was any new news... Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted February 28, 2023 Share Posted February 28, 2023 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. Link to comment Share on other sites More sharing options...
Fred Slota Posted February 28, 2023 Author Share Posted February 28, 2023 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now