Excel scripting
#1
Scooby Regular
Thread Starter
Join Date: Jun 2002
Location: Switzerland
Posts: 643
Likes: 0
Received 0 Likes
on
0 Posts
Excel scripting
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.... move to B3 compare to A3,A4... Z120 etc, repeat until all entries have been counted).
This is doing my head in
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.... move to B3 compare to A3,A4... Z120 etc, repeat until all entries have been counted).
This is doing my head in
#2
Scooby Regular
Join Date: Jan 2006
Location: Yorkshire
Posts: 1,348
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by swaussie
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.... move to B3 compare to A3,A4... Z120 etc, repeat until all entries have been counted).
This is doing my head in
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.... move to B3 compare to A3,A4... Z120 etc, repeat until all entries have been counted).
This is doing my head in
A1 = OFFICE
B1:B99 = Scanned Applicaions
=COUNTA(B1:B99,A1) = your answer
#3
Scooby Regular
Thread Starter
Join Date: Jun 2002
Location: Switzerland
Posts: 643
Likes: 0
Received 0 Likes
on
0 Posts
cheers Gazza-uk.
I tried that and a bit of everything else but found out that if I reformatted the sheet I could macro a Pivot table which seemed the business for the boss...
I tried that and a bit of everything else but found out that if I reformatted the sheet I could macro a Pivot table which seemed the business for the boss...
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
bluebullet29
General Technical
9
05 October 2015 02:17 PM