Hi guys,
I am creating a Software Asset overview in excel for the directors of our business so they can see what software we have and what computers it has been installed on. They want to have a big piece of paper that they can print off and get the 'big picture' overview. I have managed to get two reports about the software, and I would like to somehow merge the relevant information to give this overview.
So the first report I have is a list of the asset name, software name, software ID. As you can imagine, a single computer may have a lot of software installed, so this report has about 12,000 rows, looking something like this:
Sheet 1:
asset 1 | software 1 | version 1
asset 1 | software 2 | version 1
asset 1 | software 3 | version 1
asset 1 | software 4 | version 1
asset 2 | software 1 | version 1
asset 2 | software 2 | version 1
asset 2 | software 3 | version 1
asset 2 | software 4 | version 1
And so on...
The second report I have lists the software name, softwareID, publisher, and count of how many times it is installed on a device within our network.
Sheet 2:
Software 1 | Version 1 | Publisher 1 | Count: 120
Software 2 | Version 1 | Publisher 2 | Count: 39
etc...
What I need to do is copy the asset name from Sheet 1 whenever the software name in sheet 2 matches a software name in sheet 1, column 2. So, as an example, if sheet 2 has this row:
Software | Version | Publisher | Count
Dropbox | 3.8.8 | Dropbox, Inc. | 50
Then I would like to search the software column in sheet 1 and copy the computer name (Cell A) to sheet 2 every time Dropbox is listed. The real trick is that there may be 50 computers that match this criteria, and I would like them to be appended in the asset column so that sheet 2 eventually looks like this:
Software | Version | Publisher | Count | Assets
Dropbox | 3.8.8 | Dropbox, Inc. | 50 | PC1, PC2, PC3, PC4, etc
I hope my query is understandable. I am happy to answer any questions that may make it clearer.
Thanks guys!
I am creating a Software Asset overview in excel for the directors of our business so they can see what software we have and what computers it has been installed on. They want to have a big piece of paper that they can print off and get the 'big picture' overview. I have managed to get two reports about the software, and I would like to somehow merge the relevant information to give this overview.
So the first report I have is a list of the asset name, software name, software ID. As you can imagine, a single computer may have a lot of software installed, so this report has about 12,000 rows, looking something like this:
Sheet 1:
asset 1 | software 1 | version 1
asset 1 | software 2 | version 1
asset 1 | software 3 | version 1
asset 1 | software 4 | version 1
asset 2 | software 1 | version 1
asset 2 | software 2 | version 1
asset 2 | software 3 | version 1
asset 2 | software 4 | version 1
And so on...
The second report I have lists the software name, softwareID, publisher, and count of how many times it is installed on a device within our network.
Sheet 2:
Software 1 | Version 1 | Publisher 1 | Count: 120
Software 2 | Version 1 | Publisher 2 | Count: 39
etc...
What I need to do is copy the asset name from Sheet 1 whenever the software name in sheet 2 matches a software name in sheet 1, column 2. So, as an example, if sheet 2 has this row:
Software | Version | Publisher | Count
Dropbox | 3.8.8 | Dropbox, Inc. | 50
Then I would like to search the software column in sheet 1 and copy the computer name (Cell A) to sheet 2 every time Dropbox is listed. The real trick is that there may be 50 computers that match this criteria, and I would like them to be appended in the asset column so that sheet 2 eventually looks like this:
Software | Version | Publisher | Count | Assets
Dropbox | 3.8.8 | Dropbox, Inc. | 50 | PC1, PC2, PC3, PC4, etc
I hope my query is understandable. I am happy to answer any questions that may make it clearer.
Thanks guys!
