Formula- Dependent lists Multiple challenges EXCEL

Hi Forum,

I have to learn this so I can apply in my course of work. Let me list the things i need to achieve as I think many of them are related:

Step 1. Create a dependent list for validation from an unsorted list of data

Extra steps:
list only values which are not equal to (String)
Remove any blank in the list (within and at the end of a range)

Note:
Attempt to do in a formula, preferably without a helper list.

Follow-up:
I am struggling to understand how the combination of index, match offset and countif work together for a solution and would really like to understand. I do understand hows these work (at a base level) but not clicking anyone can help (\

lastly:
when i countif etc.. i do understand what information results is the concatenation of these..... (is there a step option on excel for mac....?)

EUREKA, after much coffee I have a handle on these..... - thanks to those who worked on the previous threads...