Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect duplicity by 1 scanning
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect duplicity by 1 scanning
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect duplicity by 1 scanning
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 "--== Alain ==--" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scanning into Excel | Excel Discussion (Misc queries) | |||
Scanning | Excel Worksheet Functions | |||
Scanning down a column | Excel Discussion (Misc queries) | |||
scanning a table? | Excel Worksheet Functions | |||
Scanning & .chk extensions | Excel Programming |