Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

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
0
willa666
Asked:
willa666
  • 5
  • 4
1 Solution
 
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
 
SiddharthRoutCommented:
Paste the code in a module in the VBA editor and simply run the macro "Sample"

Sid
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
SiddharthRoutCommented:
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
 
willa666Author Commented:
:) that works great thank you
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now