How do I Transpose without copying duplicates?

[Guest]

I have two colunms of data. Coloum A contains the departments within the
organisation, column B the employees.

What I am wanting to do is transpose column A onto a row but doe not want to
display any duplictes. I have tried using the paste special but that copies
all.

The only way I have found but it is a bit long winded, is to create a pivot
table and dorp the departments into the 'drop column field' then copy and
paste values, deleting all the titles and borders.

There must be a simpler way?

The Rook

 

[Guest]

One play using non-array formulas ..

Assuming dept names expected within A2:A100 (say)

Put in C2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
Copy C2 down to C100 (cover the expected extent in col A)
(Leave C1 empty)

Then we could put in say, D1:
=INDEX($A:$A,MATCH(SMALL($C:$C,COLUMN(A1)),$C:$C,0))
and copy D1 across until #NUM! appears

Alternatively, with an error trap, we could put in D1:
=IF(ISERROR(SMALL($C:$C,COLUMN(A1))),"",INDEX($A:$A,MATCH(SMALL($C:$C,COLUMN(A1)),$C:$C,0)))
and copy D1 across until "blanks" ("") appear

The list of unique depts within col A will be auto-extracted in D1 across