Compare and count
 

Hi All,
I am trying to do a software license reconciliation at work. I have written
a VB Script that searches our servers for installed software and dumps that
into a spreadsheet with the server name as the column heading and the
software installled on that server listed underneath.

What I cant figure out is how to go through this data and write the name of
the software into a new column (or sheet) with a count of how many times it
appears in the spreadsheet indicating how many licenses I need.

Some servers have different software so I need a way to scan what has
already been counted and listed against what has not yet been counted (i.e,
count how many times the string in A3 appears in spreadsheet, write string A3
and how many times it is installed, move to A4 compare to A3, compare to
software already counted and if unique count instances and write string A4
and number of instances, continue to end of column then move to B3 compare to
A3,A4....Z120,BV34... etc, repeat until all entries have been counted).

Compare and count
 

Seems to me if you wrote you data as

SERVER_NAME SOFTWARE_NAME
ServerName1 Software1
Servername1 Software2
Servername1 Software1
Servername1 Software3
Servername2 Software1
Servername2 Software3
Servername3 Software1
Servername3 Software2
Servername3 Software1

you could then use a pivot table to do your analysis and eliminate the
requirement for further coding. this assumes the rows required will not
exceed 65536 (including a header row)

--
Regards,
Tom Ogilvy


"Swaussie" wrote:

Hi All,
I am trying to do a software license reconciliation at work. I have written
a VB Script that searches our servers for installed software and dumps that
into a spreadsheet with the server name as the column heading and the
software installled on that server listed underneath.

What I cant figure out is how to go through this data and write the name of
the software into a new column (or sheet) with a count of how many times it
appears in the spreadsheet indicating how many licenses I need.

Some servers have different software so I need a way to scan what has
already been counted and listed against what has not yet been counted (i.e,
count how many times the string in A3 appears in spreadsheet, write string A3
and how many times it is installed, move to A4 compare to A3, compare to
software already counted and if unique count instances and write string A4
and number of instances, continue to end of column then move to B3 compare to
A3,A4....Z120,BV34... etc, repeat until all entries have been counted).