Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2014-01-04
Medium Priority
334 Views
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
Question by:David Phelops
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 52

Accepted Solution

Rgonzo1971 earned 1400 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
``````
Regards
0

LVL 46

Assisted Solution

aikimark earned 200 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
``````
http://www.experts-exchange.com/VP_74.html
0

LVL 33

Assisted Solution

Rob Henson earned 200 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

Naresh Patel earned 200 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

ID: 39761864

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

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
###### Suggested Courses
Course of the Month8 days, 6 hours left to enroll