Filtering Question

[Hobbs13]

I received an excel file that shows a bunch of information about our computer environment. As you can see from the picture below, the report shows the same AssetName on three different lines. It is doing this because different versions of Microsoft Office are installed on the machine and the report lists the machine on three lines to display that. I am not able to change the way this report generates, but I need to consolidate rows with duplicate AssetNames into one single line with additional colums.

In short, I am looking to combine rows with the same text in the asset tag column into a single row, and add additional columns to the row to still show each office version installed on that asset.

I hope that makes sense... Any help is appreciated, I need to give a presentation next week and this data is vital to the project.

 

[ChrisR]

can you not use the automatic sub-total option?

 

[nodroj81]

Are you ultimately trying to create a report that looks like this?

	Office Version</SPAN>
AssetName</SPAN>	Version 1</SPAN>	Version 2</SPAN>	Version 3</SPAN>
LDC-HWY-NOTAG</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>
LDC-HWY-TAG</SPAN>	2</SPAN>	0</SPAN>	0</SPAN>
LDC-HWY-…..</SPAN>	0</SPAN>	3</SPAN>	0</SPAN>
…</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>

 

[Hobbs13]

Chris - I am a novice with Excel so I am not even sure what the Auto Subtotal option is.

Nodroj81 - Yes you have the right idea.

AssetName#1 Office Version1, Office Version 2, Office Version 3
AssetName#2 Office Version1
AssetName#3 Office Version 1, Office Version 2

This way I have one asset on each row, but it still provides me all the information needed on office versions installed for that asset.

Note: I need the rest of the columns too (Manufacturer, Model, OS Version etc), just to be clear

 

[ChrisR]

Hi Hobbs,



Apologies...... I'm not sure of what version of excel you are using, but if you highlight your data and click on Data.............There should be something in there called subtotal in there.........IF you click that and you can set it to 'For every change in ..........." Choose asset name and any other options you need

I think this will do what you need ............As it will also group your data for you

 

[nodroj81]

You can create a simple report that will show this type of output. This youtube video does a great job of describing how to do this

https://www.youtube.com/watch?v=qPKFlVnzv5c

Try it out and respond if it doesn't work.

 

[Hobbs13]

Thanks guys - Using Office 2010 professional. I will try those options and report back.