Hi,
In conjunction with a Sysinteral's utility I have written a remote software inventory script I am running against computers in our company in order to capture what software has been installed on each machine. I am then taking the results from the Notepad text file Results.txt, pasting into an Excel spreadsheet.
Those results end up looking something like this:
(blank row)
COMPUTERNAME#1:
(blank row)
System information for \\COMPUTERNAME#1:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
(blank row)
COMPUTERNAME#2:
(blank row)
System information for \\COMPUTERNAME#2:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
(blank row)
COMPUTERNAME#3:
(blank row)
System information for \\COMPUTERNAME#3:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
The data appears in column B (with columns A and C having column widths of 1.71, white fill color, and no borders to create a makeshift margin on either side of the data).
All computer names start with a three letter prefix and are succeeded by a five digit number. I am also using conditional formatting in this spreadsheet to set the fill color to red and the text style to bold (and black) for each cell containing the computer name (i.e., COMPUTERNAME#1) and for each line containing the line: System information for \\COMPUTERNAME...
What I'd like to be able to do is to craft an auto filter situation whereas I can filter column B down so that it shows just the COMPUTERNAME#, the system information, and the software inventory data associated with that computer by selecting that computer's name from the auto filter's pull-down menu. In otherwords, showing everything from the cell containing COMPUTERNAME#1, in the example above, to the cell that appears just above COMPUTERNAME#2.
The number of rows appearing below each computer name is contingent on the number of software titles installed on each computer and is not a consistent number.
One idea I had for how I might be able to filter a block of information was to mirror the computer name column B (where the data is located) to column D and then fill down for all the rows associated with that computer name. I could then turn auto filter on for column D and select to filter by computer name. I believe in that scenario, all rows containing the computer name in column D would appear while hiding all other rows in the spreadsheet. There might be better/cleaner way of accomplishing this goal though. I’d love suggestions if you see a better way of accomplishing this goal.
As COMPUTERNAME#1 appears in B2 and COMPUTERNAME#2 appears in cell B143, I would want COMPUTERNAME#1 to be filled down for cells D2 through D142 and COMPUTERNAME#2 to be filled down for cells D143 through whatever cell number is one row above the cell where COMPUTERNAME#3's computer name appears.
If using the above method for filtering down to a block of information, the next challenge becomes how to best minimize the work of filling down each individual computer name in column D (as I'm working with around 400 computers). I don’t know if this could best be accomplished using a formula and Excel's fill feature or if it'd be better to use a VBA macro.
One possible formula I was playing around with, that's not quite right due to my inclusion of the static reference: $B$2, was =IF(LEFT(B2,3)="ABC",B2,$B$2).
I'm not quite sure how, formulaically, I might be able to indicate that if a cell in column B contains the computer name that it should capture that value and fill down in column D until the next cell with a computer name appears.
One other interesting tidbit of information that might be helpful is that the three letter prefix that appears before the five digit number in the computer name also appears as the beginning three letters is some of the software titles. However, the all capital case of that prefix, I believe, to be unique to just the computer name (the software titles are either lower or proper case).
The conditional formatting that fills the cell the color red and which bolds the text is unique to the two rows containing the computer name (COMPUTERNAME#1 and System information for \\COMPUTERNAME lines).
Thanks much for your help with this question!
In conjunction with a Sysinteral's utility I have written a remote software inventory script I am running against computers in our company in order to capture what software has been installed on each machine. I am then taking the results from the Notepad text file Results.txt, pasting into an Excel spreadsheet.
Those results end up looking something like this:
(blank row)
COMPUTERNAME#1:
(blank row)
System information for \\COMPUTERNAME#1:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
(blank row)
COMPUTERNAME#2:
(blank row)
System information for \\COMPUTERNAME#2:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
(blank row)
COMPUTERNAME#3:
(blank row)
System information for \\COMPUTERNAME#3:
Uptime: ...
Kernel version: ...
Product type: ...
Product version: ...
Service pack: ...
Kernel build number: ...
Registered organization: ...
Registered owner: ...
Install date: ...
Activation status: ...
IE version: ...
System root: ...
Processors: ...
Processor speed: ...
Processor type: ...
Physical memory: ...
Video driver: ...
Applications:
Application #1
Application #2
Application #3
Application #4
Application #5
Etc.
The data appears in column B (with columns A and C having column widths of 1.71, white fill color, and no borders to create a makeshift margin on either side of the data).
All computer names start with a three letter prefix and are succeeded by a five digit number. I am also using conditional formatting in this spreadsheet to set the fill color to red and the text style to bold (and black) for each cell containing the computer name (i.e., COMPUTERNAME#1) and for each line containing the line: System information for \\COMPUTERNAME...
What I'd like to be able to do is to craft an auto filter situation whereas I can filter column B down so that it shows just the COMPUTERNAME#, the system information, and the software inventory data associated with that computer by selecting that computer's name from the auto filter's pull-down menu. In otherwords, showing everything from the cell containing COMPUTERNAME#1, in the example above, to the cell that appears just above COMPUTERNAME#2.
The number of rows appearing below each computer name is contingent on the number of software titles installed on each computer and is not a consistent number.
One idea I had for how I might be able to filter a block of information was to mirror the computer name column B (where the data is located) to column D and then fill down for all the rows associated with that computer name. I could then turn auto filter on for column D and select to filter by computer name. I believe in that scenario, all rows containing the computer name in column D would appear while hiding all other rows in the spreadsheet. There might be better/cleaner way of accomplishing this goal though. I’d love suggestions if you see a better way of accomplishing this goal.
As COMPUTERNAME#1 appears in B2 and COMPUTERNAME#2 appears in cell B143, I would want COMPUTERNAME#1 to be filled down for cells D2 through D142 and COMPUTERNAME#2 to be filled down for cells D143 through whatever cell number is one row above the cell where COMPUTERNAME#3's computer name appears.
If using the above method for filtering down to a block of information, the next challenge becomes how to best minimize the work of filling down each individual computer name in column D (as I'm working with around 400 computers). I don’t know if this could best be accomplished using a formula and Excel's fill feature or if it'd be better to use a VBA macro.
One possible formula I was playing around with, that's not quite right due to my inclusion of the static reference: $B$2, was =IF(LEFT(B2,3)="ABC",B2,$B$2).
I'm not quite sure how, formulaically, I might be able to indicate that if a cell in column B contains the computer name that it should capture that value and fill down in column D until the next cell with a computer name appears.
One other interesting tidbit of information that might be helpful is that the three letter prefix that appears before the five digit number in the computer name also appears as the beginning three letters is some of the software titles. However, the all capital case of that prefix, I believe, to be unique to just the computer name (the software titles are either lower or proper case).
The conditional formatting that fills the cell the color red and which bolds the text is unique to the two rows containing the computer name (COMPUTERNAME#1 and System information for \\COMPUTERNAME lines).
Thanks much for your help with this question!
