Transpose names from horizontal to vetical

XL 2003

Am I tired so don't see solution?

Transpose names without blanks:

Book1.xls

Given duplicity of names I would say you need an Array, eg:

B3:
=INDEX($A$1:$N$1,SMALL(IF($E$1:$N$1<>"",COLUMN($E$1:$N$1)),ROWS(B$3:B3)))
confirmed with CTRL + SHIFT + ENTER
copied down

Best to conduct a COUNTA elsewhere to determine how many times the Array is required, eg:

B2: =COUNTA(E1:N1)

And use that as a precursor in B3

=IF(ROWS(B$3:B3)>$B$2,"",INDEX(....))
confirmed with CTRL + SHIFT + ENTER
copied down

You can do this the long way....

Selecting your row of data -> Copy
Select the top row of where you want to horizontally transpose it -> Paste Special -> Transpose -> Edit -> Go To -> Special -> Blanks -> Delete

Or macro it out to something like:

' Macro TransposeAndDeleteBlanks
'

Range("e1:N1").select
'Change the above range to meet your needs of what you are transposing

Selection.copy

Range("b3").select 
'Change the above range to o where you want to transpose to

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

End Sub

or just put
=COUNTA($E$1:E1) in e2 drag across then
index match

=INDEX($E$1:$N$1,MATCH(ROWS($A$1:A1),$E$2:$N$2,0)) dragged down
you can remove duplicates later with advanced filter if required.

Great!, Works!

Yes, forgot to say.. I tried to avoid extra row...

zbor, for the benefit of future users who are looking up similar topic, it is best to tell us if you tried all suggestions and which of the suggestions worked.

When I saw martinwillson I realize that it's the way I'll do usually
Well, it's end of workday, my brain stopped.

But before I saw it, I've tested DQ solution and works great (didn't doubt). However, I've need some time to adjust.

I didn't try macro (prefere non-macro solutions.. but one day I'll learn it... it's just not that day yet )

You can do macro-less using the same approach -- assuming E1:N1 are either constants or blanks (not formula nulls)...

Highlight E1:N1 -> F5 -> Special -> Constants -> OK -> CTRL + C -> B3 -> ALT + E -> S -> V -> ALT + E -> OK

Here are both solutions:

Book1.xls