Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Transpose without copying duplicates?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Transpose without copying duplicates?
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TheRook" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I transpose without duplicates? | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
Finding Duplicates | Excel Worksheet Functions | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
TRANSPOSE() | Excel Worksheet Functions |