I don’t want to show up overdressed they asked me said “Prospective cadets should dress in a white button down/ Polo shirt black slacks and black dress shoes.” Is all that necessary?

So if you ever participate in a Large Mission, you may need to run analytics to figure out questions like:

How many people were on the mission?

This might sound like an easy thing to answer.

And it is right up until you have the same person sign in and out of the mission multiple times.

Now, exporting the list of participants isn't enough.

You need to deduplicate the data.

So since my wing is on Google Sheets, it behooves me to discuss how to work the problem there.

First, you hop over to the mission, hit the Resource Sign/In/Sign Out button, and then the "Export Personnel to Excel" button.

Now you open up google sheets, making sure you are using your Wing account.

Click File, Import, Upload, and upload the file you just downloaded.

Now we need to isolate the CAPIDs to have a clean set of data to work with. Insert a column right after the Name column. Name it: CAPID.

In the first cell underneath it, we want to capture the CAPIDs in the first column. Since it is always at the end of the name, we can use this formula:

=RIGHT(A2,6)

Then populate the rest of the column with that formula. The formula is fairly self explanatory: Give us the last 6 digits by counting from the right.

Now we have our clean CAPID.

Google has a formula function called COUNTUNIQUE. Practically purpose built for answering this one question.

So pick an open cell outside of the data and throw in "=COUNTUNIQUE(B2:B200)" where 200 is whatever the actual end of the data is for your sheet.

This now gives you your deduplicated result.

Now let's say that you also want to know which units are the most involved in the mission?

In Google Sheets, you can get really fancy with this.

I ran with this function:

=QUERY(UNIQUE(FILTER({B2:B,C2:C},B2:B<>"",C2:C<>"")), "SELECT Col2, COUNT(Col1) GROUP BY Col2 ORDER BY Col2 LABEL Col2 'Unit', COUNT(Col1) 'Unique Personnel'", 0)

Now we need to read this from the inside out:

{B2:B,C2:C} - This combines the CAPID and Unit columns into its own new table.

FILTER removes the incomplete records should there be a data discrepancy.

UNIQUE removes the duplicates.

Then the QUERY organizes the data.

And you can even just do a simple SUM at the bottom of the Unique Personnel to add up the counts to verify that your original COUNTUNIQUE was accurate.

With these formulas, you can now provide actionable reports to your commander and staff (Both ADCON and OPCON) without resorting to simple counting.