Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
object or array ?
Hi,
I have a sheet in which i have several columns. for example, column A contains "publisher name", column B contains "software name" and column C contains "version" i would like to scan all records of this sheet and store result in some array, collection or objects...it depends what will be the most effective one. the process should be the following one : for each record, application should search in the object or array if the data already exist (i mean "publisher/software/version"), if it exists therefore it will increase by 1 the relative value. at the end i should be able to create an overview sheet like that : Publisher Name Software Name Licenses used Microsoft Excel 2003 5 Microsoft Word XP 2 Microsoft Outlook 2003 68 So what is the best : object ? Array or something else ? thanks Al. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
object or array ?
The best way of doing this is with collections, as they won't allow
duplicates. String the 3 fields together with a delimiter and add into the collection like so Dim coll As Collection Dim cell As Range Dim tmp As String Set coll = New Collection On Error Resume Next For Each cell In Range("A1:A3") tmp = cell.Value & "~" & cell.Offset(0, 1).Value & "~" & cell.Offset(0, 2).Value coll.Add tmp, tmp Next cell On Error GoTo 0 you can then split it up like so Dim ary Dim itm For Each itm In coll ary = Split(itm, "~") MsgBox ary(0) & ", " & ary(1) & ", " & ary(2) Next itm -- --- HTH Bob (change the xxxx to gmail if mailing direct) "--== Alain ==--" wrote in message ... Hi, I have a sheet in which i have several columns. for example, column A contains "publisher name", column B contains "software name" and column C contains "version" i would like to scan all records of this sheet and store result in some array, collection or objects...it depends what will be the most effective one. the process should be the following one : for each record, application should search in the object or array if the data already exist (i mean "publisher/software/version"), if it exists therefore it will increase by 1 the relative value. at the end i should be able to create an overview sheet like that : Publisher Name Software Name Licenses used Microsoft Excel 2003 5 Microsoft Word XP 2 Microsoft Outlook 2003 68 So what is the best : object ? Array or something else ? thanks Al. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
object or array ?
Alain
Do you have a unique list of all the variables in a table? If so you could simply use the worksheet function SUMPRODUCT, for example If you data is in A1:C1000 and your table is in F1:H20, then something like the function below in I1, copied down to I20 =SUMPRODUCT(--($A$1:$A$1000=F1),--($B$1:$B$1000=G1),--($C$1:$C$1000=H1)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "--== Alain ==--" wrote in message ... Hi, I have a sheet in which i have several columns. for example, column A contains "publisher name", column B contains "software name" and column C contains "version" i would like to scan all records of this sheet and store result in some array, collection or objects...it depends what will be the most effective one. the process should be the following one : for each record, application should search in the object or array if the data already exist (i mean "publisher/software/version"), if it exists therefore it will increase by 1 the relative value. at the end i should be able to create an overview sheet like that : Publisher Name Software Name Licenses used Microsoft Excel 2003 5 Microsoft Word XP 2 Microsoft Outlook 2003 68 So what is the best : object ? Array or something else ? thanks Al. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
object or array ?
Another way is to ... Sort by Publisher | Software then run Subtotals from the data menu. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "--== Alain ==--" wrote in message Hi, I have a sheet in which i have several columns. for example, column A contains "publisher name", column B contains "software name" and column C contains "version" i would like to scan all records of this sheet and store result in some array, collection or objects...it depends what will be the most effective one. the process should be the following one : for each record, application should search in the object or array if the data already exist (i mean "publisher/software/version"), if it exists therefore it will increase by 1 the relative value. at the end i should be able to create an overview sheet like that : Publisher Name Software Name Licenses used Microsoft Excel 2003 5 Microsoft Word XP 2 Microsoft Outlook 2003 68 So what is the best : object ? Array or something else ? thanks Al. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array - Object required??? | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |