Solved

New sheet with one of each name

Posted on 2011-03-07
9
166 Views
Last Modified: 2012-05-11
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
Comment
Question by:willa666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35059355
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
 
LVL 1

Author Comment

by:willa666
ID: 35059469
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35059480
Paste the code in a module in the VBA editor and simply run the macro "Sample"

Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:willa666
ID: 35059601
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35059611
Ok. Upload your exact file. Let me amend it upload it for you :)

Sid
0
 
LVL 1

Author Comment

by:willa666
ID: 35059670
Thank you matey
Workbook1.xlsm
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35059696
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
 
LVL 1

Author Comment

by:willa666
ID: 35059717
:) that works great thank you
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35059719
You are welcome :)

Sid
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question