Vertical to Horizontal & Eliminate Duplication

[NirOrtal]

Hi

I’m working with Excel 2007

I have a list of names in A1:A10. Some of the names are exist more than once.
I want to copy he list to C1:L1 and eliminate duplication in one action

Thanx

 

[PBezucha]

I hope it will work even in 2007. If you are free as to the space to the
right from your new header use the macro:

Sub SetHeaderFromTheList()
Dim DummyRange As Range
Range("A1").EntireRow.Insert
Set DummyRange = Range("B1:B10")
' (auxiliary column range located wherever, will be cleared)
Range("A1") = "Dummy header"
Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=DummyRange, Unique:=True
Range("A1").EntireRow.Delete
DummyRange.Copy
Range("C1").PasteSpecial Transpose:=True
DummyRange.Clear
End Sub