Solved

For loop on unique records in a list?  VBA

Posted on 2015-01-27
15
52 Views
Last Modified: 2016-02-10
Is there a way to run a procedure only on the unique records in a list without copying them out removing dupicates?
I have list in column AD and it can have random duplicate records within the list.  At first approach I was going to copy them out removing dups, but thought I would ask.
0
Comment
Question by:RWayneH
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573136
You can use the "Collection" object to collect unique objects. Subsequent entries of the same object will generate an error. This error can be trapped and ignored.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40573147
Are they in order?

If so, you can set the procedure to check each record as it comes to it as to whether it is the same as the previous record.

If not, I guess you could copy the record to a supplementary list as it gets dealt with and then have the procedure check the list to see if that record has already been dealt with.

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 40573155
Ok.. Do you have an example of this? or can we create a simple one?  Maybe for each unique record enter a MsgBox with the value in it?  Go through the whole list and exit after the last one?   I have never used this before.
0
 

Author Comment

by:RWayneH
ID: 40573171
No, these will not be in order... I wish they were...  it is not an option to sort these..   I need to keep records in the original order..  which probably makes it more difficult.  Sorry
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573180
Enter a list in D1:D13 and try this macro

Sub actuniqe()
Dim coll As New Collection
For Each cel In Range("D1:D13")
    On Error GoTo ext
    coll.Add cel, cel
    MsgBox cel
ext:
    On Error GoTo -1
Next cel
End Sub
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40573220
You can also use a Dictionary.  I write a comprehensive article on the subject here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

A Dictionary offers more flexibility and functionality than a Collection, and my testing suggests it can be substantially faster (although you would never notice the difference on small data sets).
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40573224
You could add a row identifier in numerical order, sort by duplicated records, run routine and then sort back to original order using row identifier.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40573227
Or record a macro using Filter to remove duplicates, then use that code in a loop of your own where Cells(somerow, 1).Entirerow.Hidden = False
0
 

Author Comment

by:RWayneH
ID: 40573232
It is skipping over the MsgBox cel  line.  Makes it hard to follow were it is.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40573269
Here is the file
ActUniqe.xls
0
 

Author Comment

by:RWayneH
ID: 40573306
The example file is working for me, not sure why it skipped over the MsgBox for me??  
Thanks, let me work with this and see if I can insert this into my procedure.
0
 

Author Comment

by:RWayneH
ID: 40573403
Instead of For Each cel In Range("D1:D13")
Could I use: For Each cel In Selection        or I was considering naming the range.  I have to exclude the header.
Trying to figure out how to set a dynamic list instead of a absolute value for the list.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573480
For Each cel In Selection
For Each cel In Range("D2:D" & range("D2").end(xldown).row)
For Each cel In Range("D2:D" & range("D" & rows.count).end(xlup).row)

Are various options

If you can give your scenario an appropriate option could be suggested.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40574486
Patrick has posted the best recommendation (http:#a40573220 ) -- use a Dictionary object to get unique values.

Alternatively, use the AdvancedFilter to extract a unique list of items.  Hopefully, your list has a header row.

You can techniques from my Fast Data Push to Excel article to populate the AD cells' values from the dictionary's items collection.  You will need to use the Transpose worksheetfunction and be mindful that your original AD contents will not be changed by the shorter list/array push.
http:A_2253.html
0
 

Author Closing Comment

by:RWayneH
ID: 40596004
Thanks for the help.  Worked great!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 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

12 Experts available now in Live!

Get 1:1 Help Now