New sheet with one of each name

Hiya all

I have a sheet that has lots of the same values in one coloum. I want to be able to create a new sheet that show a single coloum with only one of each of the items:

So this is and example of the sheet i have:

doors
chairs
planks
chairs
chairs
planks
chairs
planks
chairs
planks
chairs
planks

And on the next sheet i want it to ony show one of each like this:

doors
chairs
planks
LVL 1
willa666Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Here it is. The output will be generated in Sheet2

Sid

Code Used

Sub Sample()
    Dim lastRow As Long
    
    Sheets("Sheet1").Columns("A:A").Copy Sheets("Sheet2").Columns("A:A")
    Application.CutCopyMode = False
    
    lastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Sheet2").Range("$A$1:$A$" & lastRow).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Open in new window

Workbook1.xlsm
0
 
SiddharthRoutCommented:
If you are using MS Excel 2007 onwards then this will help you. Just rename the Sheets and Column names as applicable.

Sub Sample()
    Sheets("Sheet1").Columns("A:A").Copy Sheets("2").Columns("A:A")
    Application.CutCopyMode = False
    Sheets("Sheet2").Range("$A$1:$A$12").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Open in new window


Sid
0
 
willa666Author Commented:
hi SiddharthRout

I am using excel 2011 on the mac so it should support your solution. But i am used to using formuals and am not sure how i would use you soultion.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
SiddharthRoutCommented:
Paste the code in a module in the VBA editor and simply run the macro "Sample"

Sid
0
 
willa666Author Commented:
So i managed to create the macro and when i run it i get this error:
Run-Time error '9':

Subscript out of range

Open in new window

0
 
SiddharthRoutCommented:
Ok. Upload your exact file. Let me amend it upload it for you :)

Sid
0
 
willa666Author Commented:
Thank you matey
Workbook1.xlsm
0
 
willa666Author Commented:
:) that works great thank you
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
All Courses

From novice to tech pro — start learning today.