Excel Shuttle and Crew Sort

[FSC] Adm: Gryphon

Well Ok Then.
i bet a lot of you would like to test this out. just finished a pile of code on it.
the sheet work best in conjunction with IAmPicard "STT Crew Manager". export your crew data to excel then copy it to the Crew Stats tab. just keep it up to date for accuracy.
i think i have nailed down a decent sort for up to 4 shuttles that will give you close to best choice selections on all 4 shuttles. so long as the information is correct and up to date, you should get decent results.
i only hope your PC can handle it, as i did get an over memory issue today while coding some of it, but i have a bad habit of running several high memory things at once.
STT Folder containing the file

Synthetic Commander

You're right I am VERY interested to try this out.

I can't figure out a way to download the file. My work pc tries to open it in Excel online, but the file size is too large for Excel online (5mb limit). I also can't directly "save as" from this link.

Maybe store it in a shared google docs folder or something like that so we can download it?

thanks.

[BL] PShivers

I agree that sharing using Google Sheets is a better option. That way peeps can check it out a bit before grabbing it. Also, IAmPicard has a sheets addon so you can use it directly from the spreadsheet. That's how I do mine.

<TGE> Darxide

I don't know what's happening, but it won't let me download that spreadsheet. I've gotten the one from the Features Request on IAmPicard's GitHub, but that one has no macros in it so it doesn't work...

So count me as another vote for Google Sheets.

[FSC] Adm: Gryphon

code isnt the same on google sheets, and would take a lot of time to convert, even if GDoc Sheets had the equivalent coding for everything. i am presently cleaning up the code and shrinking it down or common shared cell code,
i havent had the share block problem from my shared Onedrive folder, nor had the Online File Size issue before but this has become a rather large program
so perhaps sharing the folder will work better than the file itself
STT Folder

Synthetic Commander

That worked thank you Gryphon. I was able to successfully download the file and import my crew. I'm sure the Shuttle Event Missions is the tab I need to learn to use, but I've been playing around with selecting checkboxes and trying to understand what I'm looking at, don't think I've made any progress.

<TGE> Darxide

That worked, @[FSC] Gryphon. The macros are intact and everything works. Just need to figure it out now.

Edit: Ok. I can't figure this thing out at all.
Edit2: The Clear All button on the Shuttle Event Mission tab just gives an error box with "400" in it.

[FSC] Adm: Gryphon

yeah, i am still sorting things out and trying to shrink the code footprint,
havent gotten around to a how to use page just yet,
on the shuttle selection tab only select the shuttles you want to send as it will load the active ones from top to bottom, you can change the sort depending on how you prefer, i tend to jump around or only go with what i want active at a given time.
there are still holes obviously, but at least this gives you a better look of how things will go and tells you where your best odds are.
a work in progress tho regardless. saw a need and started working on it

Synthetic Commander

[FSC] Gryphon wrote: »

yeah, i am still sorting things out and trying to shrink the code footprint,
havent gotten around to a how to use page just yet,
on the shuttle selection tab only select the shuttles you want to send as it will load the active ones from top to bottom, you can change the sort depending on how you prefer, i tend to jump around or only go with what i want active at a given time.
there are still holes obviously, but at least this gives you a better look of how things will go and tells you where your best odds are.
a work in progress tho regardless. saw a need and started working on it

The shuttle event missions tab has shuttles form last event. Do these have to be manually input? the base shuttle numbers for COM, DIP, etc seem to have some dependencies on hidden columns which I assume we're not supposed to input ourselves though.

[FSC] Adm: Gryphon

yes, on the tab directly next to the shuttle tab is the primary shuttle input,
the event shuttles tab is presently storage only
on each line Group A to Group C you can input the present event info, takes a little bit of time i know, as i dont presently have a way to auto colect that data.
thinking of adding a section for the 5 possible positions so that you have a reference at the top rather than having to scroll sidewise to double check
i have found a couple other glitches where a shuttle has 2 of the same skill type slot as it knocks off the qualification count if prior crew all ready filled their quota.
and one instance where a crew was in the line up but the skill didnt show up on the qualifyer.
but again like i said, best idea to shuttle needs presently,
if you find other issues pleese post them so i know where problem issues are. so can address them as i can.
last nights attempt at srinking the code foot print with an array string was disaster and i had to reload my saved original. i will likely have to add some hidden function columns to separate the shared code. but we will see as it evolves.

<TGE> Darxide

So is there a "go" button or something? I've filled in the shuttle names and the seat skills, but nothing happens. Checking boxes doesn't do anything other than change the one column at the end to "TRUE". Also, how do you mark crew as event or bonus crew for this event? There's plenty of problems here that I don't know the answers to.

There also seems to be a problem with the crew list importer. I imported and I've got several crew listed on the Crew Calc tab with "#N/A" across several columns of data. Other crew show all zeros.

[FSC] Adm: Gryphon

i will have to change a few things as macro deletes crew with buyback lines from the crew list effecting the L100 Data oops, wants thinking clearly at that time
also need to add in for extra crew lines on the crew calc tab, there are some colums that may be blank or list NA due to information not being available or my macro oops issue, as for the new crew recently added. the Level100 stats not being available or missing from the info list at the time. i will have to fix that on my next upload to the folder.
the macro and location of the L100 info will probably be the easyest to fix first.
i will try and remember to put in some "how to" info also.

Synthetic Commander

[FSC] Gryphon wrote: »

i will have to change a few things as macro deletes crew with buyback lines from the crew list effecting the L100 Data oops, wants thinking clearly at that time
also need to add in for extra crew lines on the crew calc tab, there are some colums that may be blank or list NA due to information not being available or my macro oops issue, as for the new crew recently added. the Level100 stats not being available or missing from the info list at the time. i will have to fix that on my next upload to the folder.
the macro and location of the L100 info will probably be the easyest to fix first.
i will try and remember to put in some "how to" info also.

I am very supportive of your efforts, this would be a huge time saver for me every faction/hybrid event. Consider making the "how to" instructions a priority so that we're able to successfully run the functions in order to give you testing feedback more effectively.

I would echo Darxide's comment that once you fill in the shuttle info and check off boxes, the only effect we see is a "true" marker in one column. don't know what crew to select or what to do with it from there.

edit: just wanted to add that the macro you included for pasting the crew to that tab is AMAZING. that is a VERY easy and effective way to update crew from iampicard.

[FSC] Adm: Gryphon

ok, just did some updates and relocation of some information so as to reduce the Code Foot Print.
added TABs; "Instructions", "Shuttle PreCalc", and "L100".
Moved and re-labeled a few TABs to better fit their present use.
in the storage location i put it in a containing folder so that it doesn't just open the online Excel. just down load the latest version folder.
cool thanks for the thumbs up on the macros, some times getting them to work just right can be a pain. succeeded in crashing the spread sheet Friday on a new macro. so had to re load my back up file to figure out what got messed up. ugh. but got it working right again. need to make a few for the new TAB additions.
Folder to STT Crew Sort

<TGE> Darxide

One error I notice right away is that it is calculating crew slots incorrectly. It's telling me I have 161 active crew, but I actually only have 141.

[FSC] Adm: Gryphon

<TGE> Darxide wrote: »

One error I notice right away is that it is calculating crew slots incorrectly. It's telling me I have 161 active crew, but I actually only have 141.

yes i thought about this, and will be addressing it, as i have a decent number of crew, my list is probably eclipsing yours. i saved a version with the crew cut back and added a quik removal button.
on each of the calc pages you will just have to use the "ReList Crew Button to repopulate the list.
just tested those functions on those pages before uploading the new set up, so i think its all good.
been tinkering with the suggested crew setting to hope-fully give a better selection. at some point i am going to have to implement a way to account for incomplete crew and have suggestion for them as to keep or airlock for early on players.

Synthetic Commander

Thank you very much for adding the instructions. I am starting to understand the functionality, though I crashed my excel at work running the macros so I'm going to have to install MS Office at home before I can do more testing and learning.

I did not realize how many dependencies there were from other tabs initially, I thought they were just other unrelated functionality to the shuttle screens. Now I am understanding better how the tabs are dependent on eachother.

<TGE> Darxide

Synthetic Commander wrote: »

I did not realize how many dependencies there were from other tabs initially, I thought they were just other unrelated functionality to the shuttle screens. Now I am understanding better how the tabs are dependent on eachother.

I've been trying to detangle the web of code in this thing and it's absolutely bonkers. I don't know how Gryphon keeps it straight. It's quite impressive, really.

[FSC] Adm: Gryphon

ROFLMAO, you should have seen it before i straitened up the code and cleared out a lot of extra clutter. had tons of notes from the STT.Wiki site all over the place linking to all kinds of trivial things.
item locations, event scoring list, farming list for galaxy events, Faction Event Start Calculator, etc.

[FSC] Adm: Gryphon

updated with the next event mission list,
made a few improvements to a couple minor things, but othes are still being worked on.
i am slowly narrowing in the prefferences and crew selection list.
other wise, how is it working for you,

Synthetic Commander

I'll walk you through how far I can figure out how to use this tool. I think I'm at the very last stage, but have not figured out how to read the last page yet.

making my own instructions here's a more brief version of what I'm doing when I download a new version of your excel sheet:
1. Open "crew pref" worksheet
- adjust "total crew slots" to mine
- on row 41 adjust for current event crew and traits(if needed)
- adjust the cadet crew list to my own (by the way, you really don't need as many cadets as you have listed depending on your fleet starbase level)

2. Run IAMPicard tool, extract full crew list to excel

3. open "crew stats" worksheet, click macro button to clear all crew data (always seems to leave a few crew left over to manually delete by the way).
- From Iampicard excel, copy all crew (your instructions say to ctrl-a, which used to work, but your macro seems to be changed now and does not work if the header's selected, so need to select all crew rows instead)
- click "paste and sort" macro button (awesome easy!)

4. open "voyage crew used" worksheet
- delete crew names in columns H7-H12. my intent for this tool's use is for events where shuttles take priority, so I don't want ANY crew excluded from potential shuttle seats. If I understand the instructions on this sheet, I must ensure these fields are blank or those crew won't be considered for event shuttles.

5. Open “Crew Calc” worksheet
- Click ReList Crew macro button

6. Open “Shuttle Precalc” worksheet
- Click Relist Crew macro button

7. Open "Shuttle Missions" worksheet
- ensure current event is updated on this worksheet (reruns may need mission skill slots updated)
- select desired missions with high % with the checkboxes

8. Open "Shuttle Calc" worksheet
- run sort shuttles macro

This seems to filter and display highest stat crew for shuttle seats, but I can't figure out how to read it. How do I tell which crew go into shuttle Group B, 2.2 for example? Does this just tell me all the crew I need optimally, but it's up to me to figure out which shuttle to put them in?

I like that if I click "frozen", it'll show me suggestions for crew to unfreeze! that's a great feature.

<TGE> Darxide

[FSC] Gryphon wrote: »

other wise, how is it working for you,

I haven't been using it because I can't get it to work.

For instance, I just grabbed the new version to test. Went to the Crew page and used the "clear all" button and it didn't clear the top 3. I tried to paste and got that same error box with "400" in it and the whole thing just stopped working again.

Edit: I added an error trap to help. Having a message box display with Err.Description gives me the error "PasteSpecial method of Range class failed".

Synthetic Commander

Darxide this happened to me too. After hitting clear all, just highlight the last 3 remaining rows by clicking on the row number itself (you can also Shift-click to highlight all 3 rows) and push "delete" on your keyboard to clear the contents of those 3 rows.

Then when highlighting the contents of the exported crew excel sheet from iampicard, make sure you DO NOT select the top row of headings. you can do this by selecting row 2, then push CTRL-SHIFT-END to highlight row 2 and all those below it to the bottom of your spreadsheet. Now ctrl-c, and you should now be able to paste this using the "paste & sort" macro button.

[FSC] Adm: Gryphon

ah kk, yeah, i left the last few lines of the crew to prevent a possible loop error.
just using the clear is perfectly fine, unless you have fewer than the remaining crew listed. when you paste in your crew it will over write them.
steps in order
1. clear crew data, (doenst have to be done after your first time crew load up)
2. open source "excel" export from the STTCM,
3. use control A and then Cntrol C. ( should work as that is all i am doing) including headers.
do not hit control A twice or you will select the entire Tab Page
4. use the macro button to paste it in. (cell paste start is A6 across the headers)

the reason your getting an error 400 is you subvert the copy when you perform other actions between copy and paste like clearing the old crew you loose the copy data. you could also possibly get an error if you are using the CSV as it doesn't keep the same output format.
yeah i know i have a few more crew for cadets then needed im just weird like that, one of my eccentricities of preferences. besides Seven is HOT and the lion share of my cdts are female. i am a guy after all. by all means, adjust to your preferred cdt crew.

Crew Calc, this page is dedicated to figuring your best crew choices, with out loosing any incomplete crew. you should use some discretion on this. as there is no perfect crew for every one.
"Relist" button is suppose to open hidden cells and resort by name then copy line 7, and paste it down to Line 700 to cover future crew at least for a few months. it will then progressivly clear lines that are blanks or give a zzz name line showing True in short name.
Line 7 should be the same as your first crew listed on the stat tab, line "7". (if you change the stat tab order, this can get seriously messed up)
for gauntlets in auto calc, it will select the top crew to each skill combination, starting as column "DY"
voyage crew calc starts at Column "BG". you should get 2+5% of your total available crew slots, less the crew that only have a solo skill pair of the top 12 skll pairs, exceptionally high solo skill pair crew need be in the top 30% of the crew base Voyage Crew.
primary Shuttle crew are calculated at column "AE", set to 2+1.5% of crew slots.
(each of these can be adjusted on Crew Pref Tab)

Check Boxes on Tabs Shuttle PreCalc, Shuttle Calc, and Shuttle Missions are all linked, check one on one tab and it will check the coresponding check box on the other tabs
shuttle PreCalc tab, "Relist" button should do the same as on Crew Calc.
once your crew is relisted,
check the shuttle box and use the sort crew button. this should bring the best crew choices to the top so the Shuttle calc tab can read them. (120 of your top crew it is not likely you will need them all but should include the top 12 of every skill grouping, enough to cover about 10 to 12 shuttles. i have all ready managed to do so recently thrpough out the first faction event we had rentals available. it was tripy to say the least)
then uncheck the voyage button. as your voyage data updates, they will be avilable in the shuttle calc tab.
the check boxes can help with hypothetical calculations also for you to make your own special crew selections with or wtithout event bonus of listed crew on the Preferences Tab, frozen, and voyage crew.

on the Shuttle missions tab, select the missions you want to run, 4 of the selected shuttles will appear on the Shuttle Calc Tab from top to bottom.
percentages on this page are based on highest to third possible crew choice in every slot if a shuttle has more than one skill group of the same type. this has some over lap of the same crew, but seems to fairly close to accurate and give you a fair ball park idea.

on the Shuttle Calc Tab, use the "sort shuttles" Button. crew for shuttles will be sorted to the top hiding lesser crew. and on first skill top to bottom.
Event Check Box, will post event crew with bonuses.
(in some events, i noticed some crew got a double up bonus from being a named crew and having traits, there are check boxes for this at column "DK & DO" and also on the Shuttle PreCalc Tab column "AX & BB" )

mission names are listed in column "BW", i had to keep the columns tight to make scrolling easier.
so each mission corresponding to one of 4 colours; Blue 1st, Magenta 2nd, Green 3rd, and Orange 4th.
* * the top 6 cells of each color group ( L , S , Z , AG ), will give a note of what skills the shuttle may need.
skill needed are followed by a digit, and/or the ampersand ( & ) with a digit.
Com 2&1 the first digit tells you the shuttle will need either 2 single Com skill crew or possibly one of the " Or " slots. the ampersand ( & ) tells you there are that number of combination slots including that skill.
Row 7, Crew Count, first box with digit is needed crew, second box with digit is assigned crew. the spac between them may show a yellow box with a greater than ( > ) or less than ( < ) symbol telling you the shuttle needs attention. if the 2 numbers are even the shuttle has its suggested crew set,
Rows 8 & 9 are hidden and contain function data, further across,
the same applies to hidden columns, holding function data.
Row 10, 1st column is Orange labled "Manual" is if you need to or choose to select crew manually. a manually selected crew will highlight in "Orange"
2nd column of colour grouping labled "Shuttle", are the suggested selections. highlighted in green with a number for the skill type primary, 2ndary, combined 1st&2nd, etc. (numbers are listed below)
3rd column of colour grouping labled with chance data, top % is present crew, bottom % is absolute best possible, the number between them is the shuttle gouping. this column will show the possible crew skill/s for each crew. single skill or double combined skill.
4th column of colour grouping has "No Lable" yet. is the skill type number
1 is the primary skill,
2 is the secondary skill,
3 is combined first and second skill,
4 is combined first and third skill,
5 is second and third skill. (very rarly used)

the "Voyage Crew Used" Tab, will keep track of those crew presently on voyage so as to keep them out of the shuttle calculations, if it is updated each time you send out, you wont have them poping up in the suggestions, untill they return and you update the list. checking the Shuttle Calc "Voyage" box will force them available.
the Voyage Tree Tab, has the STT.Wiki data set for quik reference. grouped by what they are known to lead to or solo. contents are linked on page for fast jump so you dont have to scroll.

Ship Crew Tab is experimental, i only just got the circular cell reference on that page to clear.
this tab will give you calculated Data, with specific selected crews on ships and place them in appropriate slots.

lets face it, i tryed to put the entire of the STT.Wiki in here. LOL
i had to separate out the farming location lists, cadet missions, faction event start calculator, event scoring data, sorter for pulling data from the STT.Wiki, and other various calculations i was working on. to a second file, things just got to big and i couldn't see all the tabs on one screen.

k i think i covered it all. fingers crossed.

Synthetic Commander

Ok got it, I understand the event shuttle functionality yes!

It will recommend top strength crew to even target >100% for a shuttle, so perhaps a future improvement could make the auto-recommendations reduce the total shuttle skill to less than 100% and reallocate that crew to a different shuttle for more even balance success rate (I know, that's easy for me to say! ).

This is definitely a step in the right direction and helps me understand how difficult it is to build a solution for everyone. It takes some playing around trying selecting different high % shuttles to find a good balanced 4 shuttles. There's strategy here, because depending on your crew strength you might be better off with 1 or 2 very high % shuttles running more time boosts, while the others use skill boosts on 3 hour missions the whole weekend.

Perhaps this could gradually be improvised with a similar solution like you implemented for voyages, where you can recommend crew based on the weakest voyages or the strongest or balanced. Perhaps a similar approach could be applied to shuttle crew recommendations to have as many high % shuttles as possible when desired to run speed boosts, then change to balanced shuttles for skill boosts running all 3 hour shuttles (or overnights).

If this approach were implemented (high % target shuttle success vs balanced), this could lead to recommending specific shuttle missions for optimal results.

<TGE> Darxide

[FSC] Adm: Gryphon wrote: »

the reason your getting an error 400 is you subvert the copy when you perform other actions between copy and paste like clearing the old crew you loose the copy data. you could also possibly get an error if you are using the CSV as it doesn't keep the same output format.

Neither of these is the case. Closing and reopening the sheet a couple times sometimes works temporarily.

I also get a Circular Reference warning every time I open the sheet. It doesn't tell me where it is, though.

Synthetic Commander

I'm struggling a bit as there seems to be a logic problem in some places. for example, I inputted all the missions for this event manually with marking "1" for the slot being com, dip, com AND dip, etc. A lot of the time it seems to work, well, but in some cases a single slot (for example DIP COM) is recommending 2 different crew for a mission that only has one DIP COM slot, and is missing the CMD slot recommendation at all unless I delete one of the recommendation numbers for DIP COM.

this example problem I have is on shuttle 1, row 17 and row 24 where my crew are both recommended for a single slot (in case there's a problem in the formulas on one of those rows).

[FSC] Adm: Gryphon

yeap, agreed, this event, because of the slots cris crossing each other, its giving me a lot of issues. i am having to look at things individualy to compare and enter manualy step by step.
i am going to have to rethink how some of the calculations are being done.

[FSC] Adm: Gryphon

<TGE> Darxide wrote: »

Neither of these is the case. Closing and reopening the sheet a couple times sometimes works temporarily.
I also get a Circular Reference warning every time I open the sheet. It doesn't tell me where it is, though.

i could have sworn i updated the sheet on Tuesday, apparently i missed some how. just re-updated the cloud share.
not 100% sure why your having issues with the copy and paste function. it was an issue i eliminated on the expiriment "Ship Crew" Tab, caused by 4 cells comparing to each other in circular reference. had to turn it into 10 block steps. to eliminate it and get them to slot right.
i haven't had any issue of that pop up since.
other than that i would have to see things first hand. to trace it out.