Solved

New sheet with one of each name

Posted on 2011-03-07
9
162 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now