Need for more than 7 Nested statements

[Hot Soup]

I have a seemingly simple problem that I have been struggling with for a little while.

I have a collection of data like the following:
Week1 | yes
Week2 | no
Week3 | no
Week4 | yes
Week5 | no
...
Week28 | yes

I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on.
Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched.

I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row. I am probably overlooking something and would appreciate any help.

Thanks.

[DonkeyOte]

If we assume your values were in say A1:B28 and you wanted unique values from A repeated in A30 onwards... (and assuming you can't just use Auto Filter and copy resulting output)

Please Login or Register  to view this content.

[Hot Soup]

Well that is impressive.

I could not use auto-filter so this is a big help, thanks, I doubt I would have figured that formula out on my own.