RE: OT Excel(??) macro/auditing help

["Stuart" <groups@xxxxxxxxxxxxxx>]

Wow!!  That was amazing!!  Thanks Pete, that has done exactly what I need
it
to do.

Stuart

-----Original Message-----
From: ukha_d@xxxxxxx [mailto:ukha_d@xxxxxxx] On Behalf Of
Pete Church
Sent: Fri 14 October 2005 12:02
To: ukha_d@xxxxxxx
Subject: RE: [ukha_d] OT Excel(??) macro/auditing help

Stuart,



I'm sure there are many ways to do this, but this might be a quick way:
Depending on the structure of the naming (i.e. if the build is always in
brackets at the end for instance) you should be able to extract the
application name from each record using a formula a bit like: (assuming the
name is in cell column A and the quantity is in column B)



=LEFT(A2,FIND("(",A2)).



Where "(" is the identifiable character at the end of the
application name
and the start of the  version number.  If not all applications have a
version number separated by "(" then you would need to put in an
error trap:




=IF(ISERROR(FIND("(",A2),A2, LEFT(A2,FIND("(",A2)),
likewise if there are
other separators used then you'd need more IF clauses.



If you drag this formula down the 1400 lines then each row should have the
application name in this new column. Call it "Application Name"
in the Top
Row (say cell C1)



Then set up an area to hold the unique list of Application Names by
entering
"Application Name" in a clear bit of space (say E1) and use the
Advanced
Filter technique:



Goto menu Data -> Filter -> Advanced Filter



Select Copy to Another Location

List Range = C1:C1400

Copy To = E1

Check Unique records only

Click OK



This gives a full list of the 'truncated' Application Names



Then against each one in column F use the sumif function:



=SUMIF($C$1:$C$1400,E2,$B$1:$B$1400)



that will add up all of the numbers in column B where the name in column C
matches the name in column E.  Note the use of $ signs to stop the ranges
changing as you paste the formula down column F.



HTH



Pete



Morning,

I've come to a brick wall on this, so I'm open to ANY suggestions at
present.  The scenario is I need to find out all software that is installed
on the workstations  on our desktops (circa 150 machines).  I have use
Aida32 and a logon script to create an audit list and I now have a
spreadsheet with a list of software installs and the amount.
Great so far.

The problem is due to the way Aida audits (it sees different build numbers
as different products) I have entries like:

Microsoft Office Standard Edition 2003 (11.0.5614.0)     7
Microsoft Office Standard Edition 2003 (11.0.6361.0)     46
Microsoft Office Standard Edition 2003 (11.0.7969.0)     8

and that's one of the easiest one!! Now clearly I don't care what build
number is on a machine (no for this exercise anyway) I just need to know I
have (7+46+8)=61 copies.

So ideally I need a way of telling excel or access (or any other
suggestions
- the file is CSV) to look through the 1400 lines, and group
"Microsoft
Office Standard Edition 2003 " as one entry, then count the numbers in
column B.

Like I say, I'm open to any option, so if anyone can think of anything
else,
please speak :-)

Cheers

Stuart





[Non-text portions of this message have been removed]








SPONSORED LINKS


Home
<http://groups.yahoo.com/gads?t=ms&k=Home+repair+improvement&w1=Home+repair+
improvement&w2=Computer+stuff&w3=High&w4=Improvement&c=4&s=76&.sig=TcERUCDPQ
cJLbg9mtvQGJQ>  repair improvement

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+stuff&w1=Home+repair+improveme
nt&w2=Computer+stuff&w3=High&w4=Improvement&c=4&s=76&.sig=plS9-eUUfaBXyLVFT8
7AJg>  stuff

High
<http://groups.yahoo.com/gads?t=ms&k=High&w1=Home+repair+improvement&w2=Comp
uter+stuff&w3=High&w4=Improvement&c=4&s=76&.sig=b5d547gPx7eFWNGqXQCl7A>


Improvement
<http://groups.yahoo.com/gads?t=ms&k=Improvement&w1=Home+repair+improvement&;
w2=Computer+stuff&w3=High&w4=Improvement&c=4&s=76&.sig=O6pw_oratTOwep2G_91fI
Q>







_____

YAHOO! GROUPS LINKS



*          Visit your group "ukha_d <http://groups.yahoo.com/group/ukha_d>
"
on the web.


*          To unsubscribe from this group, send an email to:
ukha_d-unsubscribe@xxxxxxx
<mailto:ukha_d-unsubscribe@xxxxxxx?subject=Unsubscribe>


*          Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/>
 Terms of Service.



_____



[Non-text portions of this message have been removed]