I’m not sure why there are so many columns. Kind of got lost there.
Let’s take a step back and think this through. How would you sort words? Text sorting happens left to right alphabetically. Throwing a number at the end of a word is going to have little to no effect on sorting because the last characters are the least important. You start by considering the first letter, then the second, then the third, and so on. @nathanaelb was demonstrating two separate columns. Not a single column that merges a word with a number.
Also, what @nathanaelb mentioned about numbers being stored as text vs numbers isn’t entirely accurate. There are plenty of reasons to store numbers in a number column. In fact I recommend it if that column will only ever contain numbers. Using math on that number should not be the sole factor to consider whether it should be numeric or text column. A number represented with a text column is very very different than a column represented with a number column. If you were to sort these values (1, 5, 10), they would sort as (1, 5, 10) if they were numeric, but as text they would sort as (1, 10, 5). Text sorts left to right, so anything that starts with 1 will sorry before 5. Numbers sort numerically based on the size of the number. Text is stored left to right while never are stored right to left. They are very different, so consider that when choosing the column type.
In your Medication Type table, add a number column and give each row a number that corresponds to the sort order that you want for each medication type. Delete all other columns, because you don’t need then. That table should only need two columns. The medication type column and a sort number column. Make sure the sort column is numeric.
In your Medications Data table, get rid of those extra columns. Add a single relation column that links the medication type in that data to the medication type in the Medication Type table. Then add a Lookup column that retrieves the associated Sort value from the relation. Use that Lookup value to sort your collection.