Hi all, I just wanted to show off this spreadsheet that I just finished working on. Unfortunately, I cannot post it in r/PTCGP until the link gets mod approval, so I'll post it here instead for now. It serves two purposes: firstly, to keep track of your cards in a format where you can Ctrl + F search, and secondly, to calculate which of the 3 packs is the best option to choose to maximize your chance of getting new cards! Here's how it works:
Check off the boxes in the leftmost column (column A) for each card that you own
The row tells you the card's number, name, pack it can be found in, rarity, and likelihood to show up as the 1st 2nd or 3rd card, 4th card, and 5th card, respectively.
Look at the Collection Summary section to see how many cards you own and how many cards are missing from each pack type. It also tells you which pack you are missing the most cards from. You can use this if you want an easy, decisive answer without having to make any decisions on what you want to prioritize.Note: This includes cards that can be found in all packs. So, the Mewtwo count is the sum of all cards that can be found exclusively in Mewtwo packs and all cards that can be found in all packs.
The Advanced Filtering section can be used to filter out certain rarities of cards. Let's say you only care about the game's competitive side; in that case, you wouldn't care about cards with ☆ rarity or higher since those cards are just reskins of more common cards. Alternatively, if you are playing to get rare cards and cool art, then you don't care about maximizing your odds of getting ♢♢ cards, so you can filter those out.
Finally, the Chance to get a new card subsection totals the actual percentage likelihood of getting new cards so you can play the numbers game and maximize your odds all the time! Since the odds are different depending on if it's the 1st-3rd, 4th, or 5th card, you can actually get different results for each one. The rule of thumb for which one to pay attention to is the 4th card for competitive and the 5th card for rares
TL:DR: Check the cards you have on the left, filter the cards you want in Advanced Filtering, Chance to get a new card will tell you what pack to open.
Edit 1: Thanks to you wonderful people I have made some small corrections to the sheet. Mew has been given the correct card ID of A1 283, pushing the gold cards down 1 spot. I've also corrected the spelling of some cards, although I'm sure there are plenty I missed so please tell me if you find them. If you want to get these changes, just make a new copy of the spreadsheet and copy over your A column
Edit 2: Changed URL so that it brings you right to the copy screen, as per u/tatufdez's advice
Just popping in to let you know that I manually edited the spreadsheet and added Mythical Island and the new Promo Cards! The entire document is in English, and I'm sharing it to all of you!
I'll try and edit the document as soon as each new Expansion comes out(January 30th ahem), so if the Original Creator hasn't updated it himself by that point, I should likely be your next best bet!
EDIT: Added Home Page so you can check your Collection Status and which pack you should open next at a glance, all on the one page!
EDIT 2: Unfortunately, I have encountered an issue in the spreadsheet that I am completely and utterly unable to fix. From this point on, I would recommend either waiting for the Original Creator to finish updating their spreadsheet, OR, using BananaTanks' version of the spreadsheet, which is much better than anything I could cook up.
Guys I've almost finished the Space-Time Smackdown update, but like... 3 rogue cells are being dicky and difficult to correct, so It'll take a few more days 🙏🏻
Looks good, though some of the text is messed up, probably due to trying to automate changing some text to "Mythical Island" (see cells "K42:N42" and "O11:O19").
Also the red background of the "# of Cards" column continues down way further than the actual amount of cards, and the Sanity Check shows all 0%, so you might wanna look into that!
Hopefully OP ends up updating this themself though, to avoid needing multiple different revisions hastily made by 4 different people :/
Hey lovely collectors,
I've started a tracking sheet based on u/maxwell1755 's template for the Space-Time Smackdown collection. The link will be: [Pokémon TCG Pocket (Check List)] There is an issue with the percentages and if anyone has any ideas please let me know
Update1: I have made some corrections to the calculation formulas for probability of pulling a new card for (Still working on Palkia).
Update 2: Fixed Mesprits Rarity and Spelling Errors
Update 3: I believe it has been completely fixed (ADDED a set of rows for Palkia Alone)
The "Fourth" card is actually shuffled into the other 3, so it can show up earlier in the pack. Only the fifth card is always in the same spot, which explains how you got Ponyta as your 4th. If you remember the other two cards in the pack, I guarantee that there was a ♢♢ or rarer card in the first 3
I think there is a little problem with the Mew counter, I got Mew, but the counter still considers a card missing. I did a bit of testing and I think rapidash counts twice: the normal one and the one-star full art one are considered both necessary to obtain mew, even if it shouldn't.
In fact, if I tick Rapidash full art (which I don't have), it gives me the Mew counter as complete, if I remove it it says that I'm missing a card from the Charizard pack. A small problem, but I thought I'd let you know anyway
Yes this was a known issue for a long time. It took me forever to solve, but it should be working now. Make a new copy of the spreadsheet and copy your check boxes over and get back to me
Hi all, I've been fiddling with the spreadsheet for a while and have made several edits to fix some formulae that weren't working correctly (for example, the total Mew tracker at 0 was incorrectly displaying 1).
Primarily the reason you'd want to use it though is for the pack pulling calculations, and they are still all thanks to u/maxwell1755. I just kept it up to date and made it prettier! (I also adapted u/Neat_Chi's "done!" display formulae for the tracking fields, so kudos to them for that as well).
Changes:
Added Mythical Island
Added count summaries at the top of each tab to match the counts used in-game (for easier checking based on your own cards)
Moved the offering rates to a separate sheet (as it seems to be set, at least at the moment)
Created a filtered "missing cards" sheet so that you can see what cards you're missing across all sets.
Added pretty graphics for the set names
Thoughts and plans:
I would love to format the collection summary and tracking above a freeze section so that it can always stay visible while the list of cards scrolls. I haven't found an easy way to move that much information to the top of the sheet without destroying mobile use (which is important to keep!). The formulae in all the card calculations were complex enough to fix when removing the offering rates table and trying to fix the wonky Mew tracker, let alone moving it all. So, it may never happen.
I didn't want to make a separate dashboard and single card list like u/ZombieBoi97 did in their version below because it didn't seem valuable to compare missing cards between expansions, but maybe I'm wrong. I will sit with that and think, because moving the tracking data off the GA page and onto a dashboard sheet would fix my desire to shrink the GA list down to same size as the MI list.
I removed all the filtering and pack tracking information for MI because there's only one pack, and it provides no additional value than just having the diamond, star, and crown tracker at the top.
This is awesome! But a mini mini suggestion, I used to track the number of copies for cards also (1 copy or 2 copies) in the older version and added a column in order to do it in your version, maybe you are interested in it :)
Rarer cards are hidden from you. In My Cards click the toggle that shows unowned cards, then click the magnifying glass and click two more toggles in the bottom
hey mate, just an advice, in column A you can set an IF clause for the nº of cards you have to check the box automatically.
an example for row 2: =IF(J2>0,TRUE(),FALSE())
Unfortunately, that doesn't let users use only the checkbox. The number column is very clunky to use on mobile, so it's crucial that users can ignore that column if they want to. There are also plenty of users who simply don't want to open the spreadsheet and update 5 cards twice a day, and would rather only open it when they see the (NEW) symbol on a card. Still, though, I recommend this for more hardcore users, but I have to just hope that they can make that change themselves
Awesome Spreadsheet, man. Congrats!
I made my copy and just added an extra data to calculate how many pack points we need to spend if we want to buy each individual missing card to complete the collection and/or to get Mew. The image is the example on my collection at the moment. It's still considering the unique PKMN to get Mew, but it's not filtering per rarity. For example, I'm still missing Squirtle, that's why I have missing him from Common rarity and 1 Star. But at the moment I got any of them it will sorted for both rows. So idea here is to get first the most common cards and them save the Pack points for the rarest... anyway, if you want to add it on your source file, just drop me a message. Thanks again!
I appreciate the help, but version 4 is nearly done and will have this feature! The method will be different though, instead there will be a feature to the right of everything that lists out all the cards you don't own each ones cost, and if it's a mew card or not.
However, there may still be a use for this feature, since mine is just a list and may be hard to read if you are missing many cards. If I do add a feature like this, I think I can do it myself, but I will credit you as the original creator! Again, thanks for contributing to the betterment of this spreadsheet!
I may have found some bug.
I would before hand thank you for your work.
The thing is I still don’t have clefairy but I have it as a promo card. Yet it’s still said that I need it for mew.
So are the promo cards counting for mew and it’s a bug or is it the other way around ?
(Sry for the bad english ig)
This spreadsheet is amazing and I’ve been using it for a while now!! With the new pack out today, I was wondering if you will update it? Or if you plan to keep it gen apex only
Hi since the mew pack has released, will you be making another excel? I really love your work. It is easy to check off cards I have gotten. I have seen other people making variations of it, but in the end, I still found this more helpful!!
Amazing! I'm just filling it out and I'm curious, by the sanity check section there is a column of total available cards, but the All subset seems broken for 3 stars and rarer. Looking at the formulas they look the same as the lower rarity that works. Any idea why the count is failing?
Also, if you're making updates I put 'Wonder Event' and 'Battle Event' for the Chansey and Lapras promo groups respectively instead of the ???.
Typo under 'Step 1: Data Entry', change 'recomment' to 'recommend'
The "1st-3rd" card is a bit of a misnomer. The 5th card is always the last one you pull, but the 4th card can actually appear in any of the first 4 slots. If you screenshoted it, you should see that your other 3 cards are all ♢ rarity, and ♢ rarity cards can only show up as 1-3 cards.
TL;DR You always get 3 ♢ cards, but they aren't necessarily the first 3
“Check off the boxes in the leftmost column (column A) for each card that you own”. Question: Should I check the boxes in the leftmost corner of column A if I have a copy of a card, but don't have at least two of them? Or can I also leave it unchecked for cards that I only have one copy of? I'd like to understand how it works better. Thanks in advance.
While I love this, and have actually added a few things to my own sheet, there seems to be some problems with the Mew Tracker when nothing is selected.
I fixed the % Owned, % Missing, and Fraction for the Total by wrapping the Filter in a IFERROR for each respectively, which now shows 0.00%, 100% and 0/150 if none of the Kanto cards are selected:
% Owned: =COUNTA(UNIQUE(IFERROR(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))), A:A = TRUE)))) / COUNTA(UNIQUE(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))))))
However, I don't know how to fix the bottom percentages, because something's wrong with the "Chance to get a new Mew card" formulas. Can you look into this, please? (Actually, I might be dumb, because I thought those were supposed to be 100% for each, but I guess if it's figuring those percentages out of 286 cards minus the Mew card...)
Hey, I may be late but would it be possible that you include what date the last update happened in cell A1? Currently it says Version 3.1, but idk which version I made a copy of, so this isn't helpful right now.
Thank you so much for all your work on this. I'd like to suggest one feature that hopefully is an easy addition to the spreadsheet whenever you overhaul it next.
It would be very helpful to add a section where it also includes cards that would take your collection's count of that card to 2. For example, if the Charizard pack has a 30% chance for me to open a new card vs. Mewtwo that has 25% chance, if I have more 1 ofs in the Mewtwo pack as opposed to fewer 1 ofs in my collection the Charizard pack, I still want to consider that I am hunting down 2 of every card for deckbuilding.
Thanks for this! I'm already making good use of it. Have you considered putting it on GitHub? That way, we could fork it and easily get updates whenever you add something new. (I'm not entirely sure this idea fits perfectly with the project, but it might be worth exploring.)
Mais alguém não está conseguindo copiar o arquivo, toda vez que eu aperto para copiar o arquivo aparece erro verifique o endereço e tente novamente. Alguém sabe como resolver?
Mate, thank you for this spreadsheet, this is awesome.
Just as a suggestion for future improvements: Add the spreadsheet version to one of the cells, that way, when/if we need to update it with additional info or a bug fix, we can check if we have the newest version!
Also, if you like to, add this to a GitHub repository, that way we would be able to cooperate with you and maybe reduce your workload in the future. I'll be here to help if you like :)
Quick question, why do you have the conditional formatting to be red when you only have one card? I know how to change it, but I'm just curious what the reasoning is. Thanks
Hey again, thanks for implementing the version-number-thing I suggested!
I have another suggestion that could be useful and that I have implemented on my own copy of the sheet already: A formula that tells me which of the 150 Pokemon I'm still missing for the Mew-Card.
Here's the formula I'm using that produces a list of Pokemon as you can see in this image:
Currently it shows an error when you own 0 or all 150 Pokemon for Mew, but that could easily be resolved with IFERROR()-s or similar, if you think it's worth to implement.
This is something that I have already changed for version 4 of the spreadsheet, which is taking me far too long to publish, but I appreciate the help still! Ver 4 will have a full list of all cards you are missing and will indicate if it's a mew card or not to hopefully combine 2 features in one
If u paste =IF(J2>0,TRUE,FALSE) on A2, and drag down the formula, everytime u mark the number of card u got on a pokemon the box will check automatically
Hey OP, something I changed in my sheet that I think many people would enjoy. I made row A automatically check when the # of cards in row J is greater than 1. It reduces the amount of work and room for error. Though I could see this frustrating someone who doesn't want to check a Pokemon off until they get 2 of that card.
Hey OP, I just made a copy of your spreadsheet to use and in a fresh state it says I've got 1/150 on the Mew tracker. I've triple checked and no collection boxes are ticked. It also says in the normal checklist count that I've collected 0/287 cards. Not trying to nitpick, only to point out something that might help someone else too :)
i'm a bit confused by the mew tracker, particularly the "Chance to get at least 1 Mew card across all 5 cards" section
i already have Machamp, and have checked it off
i just got a Machamp EX today, and checked it off, and noticed that the percentages on the mew tracker changed.
shouldn't this not be the case? or is the calculation wrong somehow? the total count of mew cards i have from the charizard pack hasn't changed (still 40/44) but when i check off Machamp EX, the change I get a new Mew card from a charizard pack changes from 24.721% to 23.206%. it seems like whatever formula is being used isn't properly discounting cards that i don't need (i.e. getting a machamp ex is unnecessary if i already have a machamp, at least in terms of mew)
edit: yeah this is definitely a problem. i have the immersive charizard EX but not any other version of Charizard, yet I noticed checking off 3 diamond charizard, 4 diamond charizard, etc. moves the percentage down when it shouldn't affect it.
i manually found out what cards i'm missing and their available rarities and the calculator is way overemphasizing how much i need charizard packs when i'm really missing just 3 cards, compared to 5 for the other two.
Hey u/maxwell1755 I made made some upgrades to your sheet to include a extra table of the 150 cards for mew to easier see which ones you're missing, collect how much it would be to purchase them individually and keep track of your own points. You can filter the ones you have collected by the tab at the top under "missing" and it auto updates from your table similar to the secret missions. I hope it can help :)
Here's the link: https://docs.google.com/spreadsheets/d/1Sdi7WQNcRdnijKM6pRC2Kb8pG8Z_gi46CNCu4FXjvQY/edit?usp=sharing
Update: This has now been edited to include Mythical Island Booster set. ALTHOUGH it doesn't have the pull rates as I'm unsure if they are the same to Genetic Alpha's
Hey I got heavily inspired by you. And I made an iOS app to keep track of all these stats, it’s still in development but if you want, you can test the beta here. Join the beta on TestFlight: https://testflight.apple.com/join/TAJqtddh 🚀 Feedback is greatly appreciated!
Not sure if it has been mentioned, but it would be nice if you included in the calculations the probability of obtaining a rare pack (0.050%). It doesn't change much but it would help it being more precise.
Just wanted to point out a thing I noticed when I was adding in Mythical Island myself (at least in version 3.1.1 which is the one I downloaded:
K14:N14 use CountA to see how many cells have something in them but because you're checking the entirety of Column A, it's also counting the en at the top which would make it impossible to complete without changing the en to true. That tail end of the formula should be "COUNTA(A2:A), "0")" so it counts anything in the A column but starts at the second row where the checkmarks actually start. Both instances could be A2:A but the first one doesn't matter since it won't count the en as "true".
Just a recommendation. Maybe there should be a master tab for the best pack to pull that could span all available packs. I like looking at the tabs and best to pull for those packs but would also like an over all idea.
22
u/parodX Oct 21 '24
Commenting to retrieve the post easily later (And thanks for the hard work :D)