Solved

How's my Logic? Ascertain Number of unique items in column of data with VBA

Posted on 2014-01-04
5
314 Views
Last Modified: 2014-01-07
Dear Experts.

My logical thinking seems to be very fuzzy.

I receive a spreadsheet of Account Customer data which varies month on month.

My VBA code needs to determine each month how many different Accounts are in the list and make a summary list.

I have come up with the  code in the attached sample spreadsheet, but I feel that I am over-complicating the issue. Can you help me by either suggesting something simpler, or by validating my approach?

I have attached a truncated file with just the relevant data included.

Thanks very much
David P.
EE-Count-Unique-Items.xlsm
0
Comment
Question by:David Phelops
5 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 350 total points
ID: 39756137
Hi,

you could use the dictionary object

Sub macro()
Dim Dict As Object
Dim c As Range
Dim k As Variant, i As Variant
Dim N As Integer


Set Dict = CreateObject("Scripting.Dictionary")
For Each c In Range("A2", Range("A2").End(xlDown))
    If Not Dict.Exists(c.Value) Then Dict.Add c.Value, c.Offset(0, 2).Value
Next c
Rows("1:" & Dict.Count + 1 & "").Insert
k = Dict.keys
i = Dict.items
For N = 0 To Dict.Count - 1
    Range("A" & N + 1) = k(N) & " " & i(N)
Next N

End Sub

Open in new window

Regards
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 total points
ID: 39756160
You can also use the unique records feature to copy the unique values of a column to a range on the same or different worksheet.
Sheets("HSBC").Range("A1:A84").AdvancedFilter Action:=xlFilterCopy, _ 
       CopyToRange:=Range("Sheet1!A1"), Unique:=True

Open in new window

Here is a link to my webinar on Advanced Filters:
http://www.experts-exchange.com/VP_74.html
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 39759058
Creating a Pivot Table on your data using the Account "Name" as a Row label would give you a list of unique names.

Using one of the various COUNT functions would then give you the number of unique items.

Thanks
Rob H
0
 
LVL 8

Assisted Solution

by:itjockey
itjockey earned 50 total points
ID: 39761823
Hi dphelops,


I don't know what mi attaching is worth or not but what you are tying to do via code is also achieve by formula. see attached file.




Thanks
EE-Count-Unique-Items.xlsm
0
 

Author Comment

by:David Phelops
ID: 39761864
Thank you everyone who has replied. All your answers have been very helpful.

The one that comes closest is the Dictionary object from Rgonzo1971.  That has the merit of replicating exactly what I needed AND I've learnt a new thing about VBA!!

I considered the Advanced filter from aikimark; that, however does not count the number of Items needed to determine how many rows to insert to fit the items above the data.  That would be a useful second step - and quick and short code.  I like that.

Also considered the Pivot Table Route, from robhenson, but that would have to be created first. I need to count the items first, as mentioned above.

And itjockey - nice formula - thanks; two things
1. My own personal preference is to avoid putting complicated formulae in spreadsheets using  VBA, if possible.
2. Again, the formula would have to be put in first in order to  determine the unique item count.

I really appreciate your interest and help.
Thanks
David P.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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