detect duplicity by 1 scanning

[--== Alain ==--]

Hi,

I'm still working on my VBA code regarding software audit report.
as I wrote in my previous post on sheet1 I have several columns.

For example, column A contains "publisher name", column B contains
"software name", column C contains "version" and column D contains
"computer_barcode"

an interesting answer to my previous post was to use collection to scan
once my sheet in order to count how many time a "publisher name +
software name + version" couple is detected/installed on computer.

However, on the same computer (same "computer_barcode") the same
"publisher name + software name + version" can be detected several times
(result of several installation but in different folders).

Therefore i would like to filter it and only count once occurence
"publisher name + software name + version" if it is detected several
times for the same "computer_barcode".

example - Something like that can be :
Publisher_Name Software_Name Version Barcode
Microsoft Access 2003 NOTE0250
Microsoft Outlook 2003 NOTE0120
Pub1 App1 1.0 NOTE0250
Pub1 App1 1.0 NOTE0250
Pub2 App2 2.254 PC0245
Microsoft Access XP NOTE0123
Microsoft Access XP PC1451
....

therefore, for the couple Microsoft Access XP we should get "2", but for
Pub1 App1 1.0 we should get "1".

How can i do that ?
i was thinking to extend my collection object but how ?

thanks a lot,
Al.

 

[paul.robinson]

Hi
You will need two collections to do this.
I didn't see your earlier posts but it would be something like this
pseudo code

Dim RealCollection as New Collection
Dim TestCollection as New Collection
Dim StringInfo(1 to 2) as Variant 'string and a count

StringInfo holds your string of information and a count of how often
that string appears.

on error resume next
For each IDString
StringInfo(1) = IDString
StringInfo(2) = 1
'see if IDString already exists
Err.clear
TestCollection.Add StringInfo(1), Key:=Cstr(StringInfo(1))
If err.number<>0 then 'if string exists
StringInfo(2) = 1+RealCollection(Cstr(StringInfo(1))(2) 'the
existing count + 1
RealCollection.Remove Cstr(StringInfo(1)) 'take out existing
record
RealCollection.Add StringInfo, Key:=Cstr(StringInfo(1)) 'put
back with new count
Else
RealCollection.Add StringInfo, Key:=Cstr(StringInfo(1))
end if
Next IDString
On error goto 0
'Test
for each Item in RealCollection
msgbox Item(1) & " count = " & Item(2)
next Item

Untested, so you might need to play with this, but you should get the
idea.
regards
Paul

 

[NickHK]

Alain,
May be this is a good time to expand to a collection of custom classes. That
way you can keep your .Count of each and other related properties together,
whilst maintaining the uniqueness of the key.
Here 1 example in VB, but still applies to VBA:
http://www.vb-helper.com/howto_collection_class.html

NickHK