Welcome Back to Digital Pinball Fans - please read this first
For latest updates, follow Digital Pinball Fans on Facebook and Twitter

Reply to thread

Message: <blockquote data-quote="Tann" data-source="post: 207838" data-attributes="member: 2327">Thanks!But there is a trick. <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite7" alt=":p" title="Stick out tongue    :p" loading="lazy" data-shortname=":p" />I downloaded the Master Data, then converted the CSV format to ODS.Just unprotect each sheet (in Tools > Protect document > Sheet), then right-click on the right of the sheet tabs, in order to unhide the "Data" sheet. (Note: if you unhide the Data, don't convert the file to PDF, it will create a PDF file of 313 pages!)So far, I use arrays functions (CTRL+SHIFT+ENTER to validate), with an Index formula (which requires to name each colum of the database, i.e. Rank, Score, ID, Table. I don't use the Platform column).Extract the score:[code]INDEX(Score;EQUIV(1;(ID=F1)*(Table=G1);0))[/code]= Get the Score for a given ID (fetched from Overview.D2) and a given Table (list rewritten on the Data Sheet, by order of release, more convenient).Extract the rank:[code]INDEX(Rank;EQUIV(1;(ID=F1)*(Table=G1);0))[/code]= Get the Rank for a given ID on a given Table.Finally, I encapsulate each formula in [IF(ISERROR)] in case the ID is not in the Top 2000 or has not played on the table (returns "0" for a missing highscore and "-" for a missing rank), so it doesn't break the Scorecard calculations of HOF points.Basically, two arrays functions per 58 tables, searching inside 116821 entries... that's why it takes about 1mn to build the scorecard (I think).Maybe it's possible to speed up the process, by optimizing the database and formulas and that kind of stuff, but I'm not skilled enough for that (by far). <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite7" alt=":p" title="Stick out tongue    :p" loading="lazy" data-shortname=":p" />And mostly, I keep in mind to make the update process of the data sheet as easy and quick as possible: actually just copy/past each column, when a new Master Data CSV is released by Farsight.If someone wants to improve the file, feel free to do it, it would be welcome. <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite1" alt=":)" title="Smile    :)" loading="lazy" data-shortname=":)" /></blockquote>

Verification

Members online

No members online now.

Total: 213 (members: 0, guests: 213)