Excel - Create a sorted deduped list from several columns

I have similar data in several columns (eg column names are Owner1 Owner2 Owner3). Some items are owned by one owner, some are joint venture companies owned by two or three owners.

I'd like to create a single list of owners aken from the three columns.

At the moment I'm doing it using a pivot table where I create a new column (starting in BJ2) containing this data:

1

1

1

2

2

2

3

...etc

and then in the next column (BK) I put =IF(INDEX(Owner,BJ2)="","",INDEX(Owner,BJ2)) which when copied own gives me all the owners in one list.

Then I create a pivot table on BK:BK which I can use to extract a sorted, deduplicated list of owners.

This is clumky, but it works... except when I try to sort the data - because then my 111222333 list gets all messed up.

So without using any macros, how can I get a sorted list from开发者_JAVA技巧 my three columnns?

The easist question is maybe how can I keep the column 111222333444 etc to stay exactly the same no matter how the data is sorted?

Thanks

Rob

Found a solution that does the trick - I make every cell in my column:

=ROUND(ROW()/3,0)

which makes it always give 111222333 no matter how I sort the data.

But if anyone has a more elegant way of creating the de-duped list from several columns I'd love to hear it. Thanks, Rob