Hi all,

I have a list of text cells in column A, the list is a dynamic one that changes depending on the property selected in a cell of another sheet.

Example data... Column A is Block Name, Column B is Room Number.

---------A----------------B-------
1--Main Building-------001
2--Block A--------------001
3--Block A--------------002
4--Main Building-------002
5--Block B--------------001
6--Block A--------------003
7--Main Building-------002a
8--BSS House----------BSS
9--Main Building-------003


What I need is for a formula that lists one example of each instance included in Column A.

As per the example data above, the expected results when dragging down from C1 through to C9 would be:


---------A----------------B--------------C--------
1--Main Building-------001-------Main Building
2--Block A--------------001-------Block A
3--Block A--------------002-------Block B
4--Main Building-------002-------BSS House
5--Block B--------------001-------
6--Block A--------------003-------
7--Main Building-------002a-------
8--BSS House----------BSS-------
9--Main Building-------003-------


I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.

Any help greatly appreciated and many thanks in advance!

Jason