object or array ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

--== Alain ==-- - 11 Dec 2006 11:29 GMT

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 to this Message

Bob Phillips - 11 Dec 2006 11:50 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi,
>
[quoted text clipped - 23 lines]
>
> Al.

Reply to this Message

Nick Hodge - 11 Dec 2006 11:57 GMT

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))

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> Hi,
>
[quoted text clipped - 23 lines]
>
> Al.

Reply to this Message

Jim Cone - 11 Dec 2006 12:35 GMT

Another way is to ...
Sort by Publisher | Software then run Subtotals from the data menu.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"--== Alain ==--"
<nospam@noemail.com>
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 to this Message