Solved

For loop on unique records in a list?  VBA

Posted on 2015-01-27
15
51 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 31

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 31

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 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

19 Experts available now in Live!

Get 1:1 Help Now