Getting the ids
Open the Baseline Questionnaire Export “Your baseline questionnaire export.xlsx” or similar in Excel of LibreOffice Calc
Select row 3 which is a header for the data.
Click “Data”->” Filter”
On E3 “Completed” click the filter button and selected only “Yes”
The rows are now filtered and the ids are in column A.
Formatting the ids
Select the ids in column A, press CTRL+C to copy the ids and paste them into Notepad ++ (https://notepad-plus-plus.org/) or similar text editor (do not use Windows Notepad)
This gives you a single column with each id on a separate line. Now we need to convert this into a single line where ids are separated by the pipe character, “|”.
Select all the ids then press CTRL+H for Find and Replace. Then Select “Extended (\n…” for the “Search Mode”. Enter “\r\n” in the “Find“ box, and “|” in the “Replace“ box. Then click “Replace All”.
You will obtain a list similar to this “12511|12523|12524|12525|12543|12544|12548|12549|12551|12552|12553|12538|12554|12555|12556|12558|12561|12562|12563|12514|12539|12528|12564|12536|12529|12531|12516|12532|12533|12449|12520|12508|12527|041.0018.001090|12571|12578|12579|12580|12581|12582|12583|12584|12585|12586|12590|12510|12547|12512|12542|”
Delete the trailing “|” off the end leaving “12511|12523|12524|12525|12543|12544|12548|12549|12551|12552|12553|12538|12554|12555|12556|12558|12561|12562|12563|12514|12539|12528|12564|12536|12529|12531|12516|12532|12533|12449|12520|12508|12527|041.0018.001090|12571|12578|12579|12580|12581|12582|12583|12584|12585|12586|12590|12510|12547|12512|12542”
Filtering by ids to build a clean dataset
Open the OSTRC Log “ostrcLog_2020-04-27_2020-12-06.xlsx” or similar with the Open Source LibreOffice Calc (https://www.libreoffice.org/). Excel works too but the process is more complicated.
Select row 1 which is a header for the data.
Click “Data”->” Auto Filter”
On C1 “Athlete id” click the filter button
Select “Number Filters“ “Standard Filter…”
For “Field name” select “Athlete id”. For “Condition” select “=”. For “Value” I pasted the formatted ids from the previous section:
“12511|12523|12524|12525|12543|12544|12548|12549|12551|12552|12553|12538|12554|12555|12556|12558|12561|12562|12563|12514|12539|12528|12564|12536|12529|12531|12516|12532|12533|12449|12520|12508|12527|041.0018.001090|12571|12578|12579|12580|12581|12582|12583|12584|12585|12586|12590|12510|12547|12512|12542”
Click “Options” and select “Regular expressions”.
Click “OK”.
A cleaned data list is automatically displayed and can be saved for further analyzes.