software

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

One play using non-array formulas ..

Assuming dept names expected within A2:A100 (say)

Put in C2: =IF(A2=quot;quot;,quot;quot;,IF(COUNTIF($A$2:A2,A2)gt;1,quot;quot;,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))),quot;quot;,INDEX($A:$ A,MATCH(SMALL($C:$C,COLUMN(A1)),$C:$C,0)))
and copy D1 across until quot;blanksquot; (quot;quot;) appear

The list of unique depts within col A will be auto-extracted in D1 across
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;TheRookquot; wrote:
gt; I have two colunms of data. Coloum A contains the departments within the
gt; organisation, column B the employees.
gt;
gt; What I am wanting to do is transpose column A onto a row but doe not want to
gt; display any duplictes. I have tried using the paste special but that copies
gt; all.
gt;
gt; The only way I have found but it is a bit long winded, is to create a pivot
gt; table and dorp the departments into the 'drop column field' then copy and
gt; paste values, deleting all the titles and borders.
gt;
gt; There must be a simpler way?
gt;
gt; The Rook