paul.robinson
12/12/2006 3:32:00 PM
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
--== Alain ==-- wrote:
> 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.