Free Microsoft Excel 2013 Quick Reference

Create a unique list from column of data in excel using vba

hi,

I keep running into this same situation time and time again so this time i thought i would post the question here and see what others come up with.

I have a column of values in an excel sheet. I want to create a unique list of those values to paste into another worksheet. In the past i have used someething like this

    SourceRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetCell, Unique:=True
the problem is that the unique list that is generated includes the header from the original list.

So my question is what is the best way to generate a unique list from a column of data in an excel sheet and i want to put this unique list into another sheet starting at some cell location and have it paste down the column.

Are there any suggestions?


Post your answer or comment

comments powered by Disqus
I currently have a master file with data and copy copy my master file over 17 other excel files to update them monthly. How can I create a Vlookup from each separate file to the columns of data that I need in the master file even when the master file is closed?

Thanks for the help.

Hi

I have these tabs on a spreadsheet;

Wk1
Wk2
Wk3
Wk4
Wk5

On each tab column A contains lists of data.

On tab "summary" I want a unique list from all of the above tabs' data, starting in cell A1 and going downwards. The data on the above tabs will change so I will need to re-run this periodically.

I need this to be in macro form as the end user's with wreck it otherwise!!

Can someone help me, I have a column full of data ( column A) and I have a list of strings for example (Gotoschool "ASP"). the list will always contain Gotoschool with "X" being different. I to determine if any of the list strings are in Column A. the data in column A are mixed of words and numbers and all sort of characters. I am trying to do this in excel using VBA.

Hi all,

It´s time for another powerful approach with ADO & SQL which we here use to create a unique list with.

As the list gets larger the more powerful this approach becomes.

What we need:
* MS Windows 2000 or above
* MS Excel 2000 or above
* MDAC 2.5 or above (ADO Library)

Step 1
Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.

Step 2
Insert following code in a standardmodule:

	VB:
	
 
Sub Create_Unique_List() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stCon As String, stSQL As String 
    Dim i As Long, lnMode As Long 
     
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = wbBook.Worksheets("Sheet1") 
     
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & wbBook.FullName & ";" _ 
    & "Extended Properties=""Excel 8.0;HDR=YES"";" 
     
     'The keyword DISTINCT generate a unique list in the SQL-statement."
     'All the column in the worksheet Sheet1 have fieldnames in the first row
     'which we all use here.
    stSQL = "SELECT DISTINCT Dep, Reg, Amount FROM [Sheet1$]" 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    cnt.Open stCon 
    rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic 
     
     'A check to see that records actually exist.
    If Not rst.EOF Then 
        With Application 
            .ScreenUpdating = False 
             'Collect the present calculation-mode.
            lnMode = .Calculation 
            .Calculation = xlCalculationManual 
             'Add a new worksheet
            Worksheets.Add Before:=wsSheet 
             'Copy the records to the added worksheet.
            ActiveSheet.Cells(2, 1).CopyFromRecordset rst 
             'Reset the calculation-mode.
            .Calculation = lnMode 
            .ScreenUpdating = True 
        End With 
    Else 
        MsgBox "No records could be find!", vbCritical 
    End If 
     
     'Cleaning up
    If CBool(rst.State And adStateOpen) Then rst.Close 
    Set rst = Nothing 
    If CBool(cnt.State And adStateOpen) Then cnt.Close 
    Set cnt = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Kind regards,
Dennis

Hi all,

quick one... I'm looking to try and create an 'active' list of names from a singular column of names which does contain duplicates ie:

Smith
Smith
Jones
Walker
Jameson
Walker
etc..
etc..

say this list is in column A. now what I want to do is create a second list from column A into say column B (or on another worksheet which wil be the most likely outcome), but the list should not be a direct copy, just a list of single instances ie:

Smith
Jones
Walker
Jameson
etc..

the list from the original column A may be appended, so I'd like the function to view the range A:A constantly, so the concised listing will also be up to date - we just have quite a few rows which have the same surname on them.

Does anyone know how to do this using standard Excel functions? I'm a bit rusty and looking for help on this. Tried INDEX options and LOOKUP/VLOOKUP and I'm trying to avoid using macros (simply because my boss is a newbie when it comes to coding - I'm ok with it myself LOL) - basically it is a filter to find and check for duplicates, and only 'report back' names once.

all help appreciated - thanks

I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.

And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.

Hi,

I have a list of data where sales are by weeks across columns and stores are in rows. I want to be able to transpose columns of data in rows. is there a excel add in to transpose those column data in rows? For each store I have to copy the store for 52 times as there are 52 weeks of data. Then I am using a VLOOKUP and MATCH function to copy the amount in the rows. Is there a more efficient way to create a list of unique records in rows or transpose the data in rows? Please see attached excel sheet for my problem. Thanks.

Original Data
Store Week 1 Week 2 Week 3 Week 4
Danforth 49,457 50,278 48,173 54,061
Eglinton 212,394 206,661 213,128 197,135

Expected Format
Store Week Amount
Danforth Week 1 49,457
Danforth Week 2 50,278
Danforth Week 3 48,173
Danforth Week 4 54,061
Eglinton Week 1 212,394
Eglinton Week 2 206,661
Eglinton Week 3 213,128
Eglinton Week 4 197,135

I have a multi page workbook with items in different categories. I have created a sheet that pulls in any row that had a number in the ordered column. What I need now is a way to have another sheet create a pick list from the order sheet. I only need three of the cells in each row brought over but the cells in the pick list sheet are in different places than the order sheet. For example, on the order list I have columns A-G with headings for item#, description, price, etc. I only need to bring in the item#, description and number ordered to the pick list. Can someone please help me with the code for this? I've attached a small sheet that demonstrates what I have so far. Thanks in advance for any help!Test Order.xlsm

I have a list of items in AA1-AA50 that may appear more than once. This list come from several different cells in the worksheet that can change. I would like to create a unique list from here only showing each item once. Can you help?

Hi,

Can any help me please to extract strings of text from Columns of data(text) ?

What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.

As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

Any assistance on this matter would be grateful appreciated

Many thanks

Rob aka welshmagic69

Like in Word, we should be given the ability to wrap colums in the following
cases:

1) While working on them, for long columns of data, the split screen does
not cut it (no pun intended).

2) When we print, to save on paper. It is such a shame to have several rows
of data with many colums be forced to print on (2) pages with so much white
space. What a waste of good paper.

Just my 2 cents. I tend to have no more than 15 rows of catagory, but a
long list of culumns for forecasting reasons (weekly reported data over a 1
year period).

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I have to create a graph holding 4 sets of data, two bar charts and two line
graphs. The other problem is that they are measured in different units - is
it even possible to plot this on a single chart?

Like in Word, we should be given the ability to wrap colums in the following
cases:

1) While working on them, for long columns of data, the split screen does
not cut it (no pun intended).

2) When we print, to save on paper. It is such a shame to have several rows
of data with many colums be forced to print on (2) pages with so much white
space. What a waste of good paper.

Just my 2 cents. I tend to have no more than 15 rows of catagory, but a
long list of culumns for forecasting reasons (weekly reported data over a 1
year period).

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I'm trying to record a macro that will take a simple column of Excel data and parse it out into individual lines (similar to a tab-delimited file, except with instead of . I need to paste this column of data, entry by entry, into a terminal program (Citrix ICA Client), with a short delay (for terminal response) between each line. This seems like it should only need a simple macro to complete, but I'm not very efficient with macros. Maybe I first need to save as a tab-delimited file--but then what? For example, I ORIGINALLY have this column of data in Excel:

100
101
102
103
104

I eventually need to end up with the following:
100
101
102
103
104

I would prefer if this macro were dynamic. Sometimes I have only 20 rows of numbers, but sometimes I have up to 150 rows. Thanks!

I am new to the forum, so hopefully my question will be clear enough to understand what I am trying to accomplish.

I am trying to set up a workbook that will essentially compare 2 sets of sales data--list A is what the sales people have reported back to my team, while list B is what SHOULD have been reported back (lists are in 2 different sheets, and list B is further broken into wins and losses).

The linking point between the 2 lists is what we can call a reference number. I want to compare the 2 lists, and then report out into different sheets 1) those that are matches in the two lists 2) those in list A, but not B, and 3) those in list B, but not A. Ideally, all 3 columns of data (reference number, account name, salesperson) would be pushed into the separate sheets.

Can anyone provide any suggestions? I have played around with isna(match()) that returns FALSE if the reference number is found in the other list, and TRUE if not found. But how can I then pull all TRUEs into a new sheet without it being a huge mess? Hopefully this is an easy solution that I have overlooked. Thanks for your help.

Is it possible to compare two columns of data in Excel. For example if I
have two columns of names A & B, with column A containing 5,000 names and
column B containing 1,000 names, how would I compare B to A. Additionally,
would it be possible to then conditionally format the duplicated item in
Column B, maybe make it bold or red?

Thanks

Howard


Hi,
I am new to this forum. I am struggeling with creating multiple charts from one set of data in Excel using VBA.
I am able to create my first correct charts by:


	VB:
	
 
Set chtChart = Charts.Add 
With chtChart 
    .Name = "ANA_" & Counter1 
    .ChartType = xlXYScatterLinesNoMarkers 
    .SetSourceData Source:=Range("='Sheet1'!$E$3:$E$24"), _ 
    PlotBy:=xlColumns 
    .HasTitle = True 
    .ChartTitle.Text = "=ANA_" & Counter1 
    .Axes(xlCategory, xlPrimary).HasTitle = True 
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis" 
    .Axes(xlValue, xlPrimary).HasTitle = True 
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis" 
End With 
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$4:$A$24" 
 'Adding new Series
With ActiveChart.SeriesCollection.NewSeries 
    .Name = Sheets("Sheet1").Range("F3") 
    .Values = Sheets("Sheet1").Range("F4:F24") 
    .XValues = Sheets("Sheet1").Range("'Sheet1'!A4:A24") 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This causes an error message.
Any help would be highly appreciated.

I am trying to sort a column of data in excel. The cells to be sorted have
formulas with multiple “if” and “hlookups” statements.

I went through the sort wizard but I do not find the data sorted. Do you
know of any limitations of sorting with formulas contained in the cells?

Here is an example cell:

=IF(HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)="",NA(),IF(HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)="Calendar Day",NA(),HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)))

I am trying to sort a column of data in excel. The cells to be sorted have
formulas with multiple “if” and “hlookups” statements.

I went through the sort wizard but I do not find the data sorted. Do you
know of any limitations of sorting with formulas contained in the cells?

Here is an example cell:

=IF(HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)="",NA(),IF(HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)="Calendar Day",NA(),HLOOKUP("Calendar
Day",Results!$6:$146,ROW()-13,FALSE)))

Interrupting long calculations in Excel?

Hi all,

I am having a very long calculation in Excel using VBA.

Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA
program.

But I found due to too long running process, the Excel window stop to
respond for very long time,

besically it's like "not responding" situation with "not responding"
on the windows title bar...

It doesn't seem to accept any more key strokes, not to say ESC or CTRL
+BREAK.

What can I do to rein/stop my program?

Thanks

how to paste a picture into the header of spreadsheet in Excel 2000

PLEASE HELP ME!!!!! How can I create a line chart with two X-axes in Excel?

Hi Dear

Flipping of data in excel cell at 180 degree -
excel command required.

Regards
Nasir Sajid

I have a column (or row) of DATA as Attachment. I want to make a column (or row) of ONLY UNIQUE LIST from the DATA. Any ideas and how many ways to do?


No luck finding an answer? You could always try Google.