Creating a matrix on a new sheet from data on another

[badlydrunkboy]

I wonder if you experts can offer me some help. I've been tasked with doing an application audit of all the assets on site so I will be receiving spreadsheets with a list of assets and all the applications installed on that machine. I would like to take the asset numbers (the information in column A before the " ") and list them on a new sheet (removing duplicates) then merge the Product and Production version and move them to the new sheet (again removing duplicates), arranging them, alphabetically, as column headings with the text going vertically.

Then i would like an "x" put in each cell where that asset has that application.

Is this even possible?

I have attached an example of the way in which i receive the information.Example Audit Data.xls

Any help would be hugely appreciated.

[MickG]

Try this:-
Results sheet3.

Please Login or Register  to view this content.

Regards Mick

[badlydrunkboy]

That's amazing, thank you. two questions though;

1. is there any way of sorting the applications into alphabetical order for easier reading?

and

2. Would this be reusable for other documents where the number of assets and applications will differ?

Again, huge thanks for your help so far

[MickG]

Try the below for added code to sort "Applications" (Row 1).
This code should work for any size of number of Rows as long as the same basic sheet format is used.

Please Login or Register  to view this content.

Regards Mick

[badlydrunkboy]

Fantastic! Thank you so much.

Just one more thing i've noticed. The Product and Production version columns on the spreadsheets i'll be getting are actually going to be columns E and F respectively (I deleted some columns to hide sensitive info) but I can't work out how to refer to these instead of C and D. Could you explain what I would need to change to correct this?

Many thanks.

[MickG]

You should just be able to alter the code line as shown below in Red.

Please Login or Register  to view this content.

[badlydrunkboy]

That was my first thought but when I do that, it creates the new sheet but doesn't populate any data into it.

[MickG]

Sorry , slight oversight there, I forgot the "Dn.offsets" further down needed altering.
Try this:-

Please Login or Register  to view this content.

Regards Mick

[badlydrunkboy]

Ahh, the old Dn.offsets.

Amazing work, thank you mate.

[MickG]

You're welcome
Regrds Mick

[badlydrunkboy]

Some people are just never happy.

I've had a request to add the Name from column B to the new sheet, either into column B, therefore just shifting all the application names and values along one, or by adding it to the contents already in column A, e.g. "L037845 Joe Bloggs". I'm usually ok with merging the vaules from two cells but I can't see in the code exactly where the vaule in A is found. Also The name of the original sheet (CS) will change for each workbook so I'm not sure how to find the value in B that relates to the value in A without refering to the sheet name.

Really sorry about this.

This is the code I have right now:

Please Login or Register  to view this content.

[MickG]

I'm not sure what you want , because Column "B" of sheet "CS" contains "Software Manufacturer" and That has as many different Names as there are Values of Column "A" Number, or perhaps I'm misunderstanding !!!!
Example:- For Column "A" value "LO37845" ther are 6 different names , see below.

Please Login or Register  to view this content.

Regards Mick

[badlydrunkboy]

Sorry Mick, that example sheet has columns missing. The one i'm actually using this macro on has the headers shown below

Columns.JPG

[MickG]

Can I then assume that any Unique number in column "A" will have the same matching unique Name in column "B".
Regrds Mick

[badlydrunkboy]

Column A is the asset number of the machine and column B is the person that asset is assigned to. The asset numbers are unique but one person can have more than one asset assigned to them and, as in the example above, the SHARED value may apply to a number assets. Does that make sense?

[MickG]

Try this:-

Please Login or Register  to view this content.

Regards Mick

[badlydrunkboy]

Does that rely on the sheet containing the data being called CS? If so, it won't work because the name of that sheet will change each time.

[MickG]

Change the following at top of code:-
From this:-

Please Login or Register  to view this content.

to this:-

Please Login or Register  to view this content.

[badlydrunkboy]

Brilliant, that works perfectly. Thank you so much!

[MickG]

Your welcome
Regrds Mick

[badlydrunkboy]

Mick,

Sorry to be a pain but I have noticed that some of the results from the macro are incorrect. For example, the last asset number on my list (PC039885) has a 23 applications against it shown in the table. However, when the macro is run, the x's are not shown against the correct applications for that asset and there are only 19.

Would you be able to take a look at it for me?

If an example sheet would help, let me know and i'll create one. (A lot of the applications are very specific to where i'm working so it would take some work to remove these.)

This is the code I'm currently running:

Please Login or Register  to view this content.

[MickG]

On My sheet "PC039885" shows 18 assets and returns 18, however the the "c" variable count in the sort code is one short.
Try altering the line below to add "1" as shown in red.

Please Login or Register  to view this content.

Regrds Mick

[badlydrunkboy]

Fantastic, thank you!

[badlydrunkboy]

Hi Mick,

Sorry to bring this one up again but I was wondering if you could help. Some of the applications in the table are going to be replaced with new versions etc. How would I extend the range of fields that the macro puts together to put in Row1 on the Matrix? For example, all current versions of Firefox will be replaced by Internet Explorer 9 so I would like to the application name (Firefox) in row 1 to be struck through but have the new application name (Internet Explorer 9) after it, in the same cell.

For example,

[Strikethrough]Firefox 15[/Strikethrough] Internet Explorer 9

So strikethrough doesn't work on here but you get the idea. I would be adding two new columns to the Audit table, after column F to put the new application name.

Many thanks in advance

[MickG]

Try this:-
New Code added at bottom of code.

Please Login or Register  to view this content.

[badlydrunkboy]

Thank you. That works great for Firefox but there will be a lot of replacements over time. I was kind of hoping to, manually, insert 2 columns after column F in the attached jpeg called "New Product" and "New Product Version" and put the new info in there and strikethrough the values in the previous two columns. Then, the matrix macro would just need to pull the combined value of E2, F2, G2, and H2, for example, into the column header.

Does that make sense?

[MickG]

Try this Based on addition of new columns "G & H"

Please Login or Register  to view this content.

[badlydrunkboy]

When I run that I get following

[MickG]

Can you send a copy of your file

[badlydrunkboy]

Here you go

[MickG]

I've Attached and Run the code, Results sheet "Matrix"

[badlydrunkboy]

Thank you for this but there are a couple of issues.

1) If i put "Office" in G:30 and "Standard 2010" in H:30, it doesn't show in the Matrix in AM:1 as I would expect but if you also enter "Office" into G:37 and "Standard 2010" into H:37, cell AQ:1 displays correctly but still AM:1 doesn't.

2) There seems to be a , before the name of the new application. Can that be removed? It just looks a little weird but when I tried to change it in the code things went a bit mental

Many thanks again.

[MickG]

Not sure where the "a" is that you're referring to ????
If I'm understanding you question correctly then:-
The problem with your "Office" and "Standard 2010" in columns "G & H" is:-
Columns "E & F" have Unique data in the 2 columns that repeat periodically down the column, but unless the Data in "G & H" is Unique with E & F" then the results will be seen in the Header as the last Values in "G & H" that relate the "E & F".
Example:-
If first instance in column E & F" = "Office" and "Professional XP" and columns "G & H" = "Office" and "2010" then for each "Office" & "Profesional XP" in columns "E & F" then the results in "G & H" must be "Office" and "2010" . The code can't handle something else unless you want to add a new column (Row 1) in sheet Matrix for each different version of "G & H".
PS:- you need to add the line in red below to remove "Strikethrough" for each run of the code.

Please Login or Register  to view this content.

[badlydrunkboy]

Hmm, strangely enough, it seems to be doing what I would expect now

My first question was badly worded, sorry. there is a "," before the name of the application. Can that be removed?

[MickG]

I was using it to Differentiate between "E & F" data and "G & H" data to know how far to to take the "strikethrough."
I have now replaces it with a double space (" "). This seems to work Ok.
New code with double spaces.

Please Login or Register  to view this content.

[badlydrunkboy]

Perfect, thank you!

[MickG]

You're welcome
Regrds Mick