Separating Data

**coletteno1** · 09-17-2010, 05:56 AM

Hi Guys,
I hope you will be able to help me with this problem.
I get output from servers which I am trying to get useful data from to put into a database.
I want to take out the server name and the applications that run onto it but there are many Servers so doing it manually is exteremly time consuming and a lot of the output is information which I want to ignore.
There are also applications that I want to ignore such as software patches so if there was a way to specify which ones to ignore that would be great.
Also, some servers do not have any applications on them so this would also have to be taken into account.
I have uploaded an example (using dummy data)
On the second sheet I have uploaded how I would like the output (if possible) so I can run the macro on it to normalise it before entering it into the database.

Thanks in advance!

*Sorry for spelling separating wrong in the title!

**Andrew-R** · 09-17-2010, 06:01 AM

Is it always the case that the server name starts with \\ and that those characters aren't elsewhere in the data? Also, does the list of applications always run from the title "Applications:" to the start of the next server name? Do you have any servers with no applications?

**coletteno1** · 09-17-2010, 06:08 AM

-Yes the Server Name always starts with \\ and as far as I can see is the only use for them (not mentioned anywhere else apart from the beginning of the server name.)
-Yes the list always runs from the title 'Applications:'
-Yes there are some servers with no applications

**StephenR** · 09-17-2010, 06:16 AM

Try this:

Please Login or Register  to view this content.

**coletteno1** · 09-17-2010, 06:25 AM

Thanks!
It works for my example but when I try it on the real data it only copies the server name.
Would this be because the Applications in the example are called 'Application 1' etc whereas on the real data they are called like McAfee etc?
Thanks for the help so far
Colette

**coletteno1** · 09-17-2010, 06:30 AM

sorry if I wasn't clear about that

**StephenR** · 09-17-2010, 06:33 AM

Colette - yes, that will be the reason. There isn't much point in uploading an example which doesn't reflect reality. So, what will the names actually be or how might we tell Excel to look for them? Will there still be an "Applications:" line?

**coletteno1** · 09-17-2010, 06:37 AM

Sorry about that.
Yes there is still an 'Applications:' Line but the name of the applications that follow will be different application names like 'McAffe', 'Notepad' 'Apache Tomcat' etc

**Andrew-R** · 09-17-2010, 06:41 AM

Try changing the line

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

**StephenR** · 09-17-2010, 06:55 AM

Andrew-R: good teamwork!

**coletteno1** · 09-17-2010, 07:01 AM

Hello,
Thanks for the fast response, when I change the line it throws an error:
"Runtime error '1004' Application-defined or object-defined error'
But when I check the output it seems to have worked as far as I can tell. :D

This is great, is there a way of telling the script to ignore certain applications?
For example there are many security updates that I want to ignore and not have in the output table.
Thanks
Colettte

**Andrew-R** · 09-17-2010, 07:36 AM

Originally Posted by StephenR

Andrew-R: good teamwork!

Sorry for hijacking your code. Mine was a bit more complex, so you pipped me to the post, so I was desperate for some of the glory

**StephenR** · 09-17-2010, 08:03 AM

Not at all. Well done for deciphering my code! Btw no reason why you shouldn't post your solution too.

Colette - you could add an If statement inside the Do-While loop, e.g. if <>"Fred" then...

**coletteno1** · 09-17-2010, 08:52 AM

or could I create a list of applications to ignore and then include this in the script?
Thanks guys you have been a great help so far!

**coletteno1** · 09-17-2010, 08:54 AM

also how could I remove the semi colon after each of the server names in the output?

**snb** · 09-17-2010, 09:23 AM

This will be sufficient:

Please Login or Register  to view this content.

**coletteno1** · 09-17-2010, 09:48 AM

Originally Posted by snb

This will be sufficient:

Please Login or Register  to view this content.

Hi snb, could you explain this code to me? Sorry I'm a complete newb to VB.
Colette

**snb** · 09-17-2010, 09:52 AM

It gives the result you asked for (if sheets(2) is empty)
It takes 2 pages to explain.
Please test it first in your application.

But I think (combined with the other question you had) that it is better to explain what you want eventually. My impression is that you are asking about steps between in a larger process. Those steps may turn out to be unnecessary.
The 'flatter' the data, the easier to manipulate. What you asked for in this post is adding a structure to the data. That makes the data more complex and may complicate the handling later on.

**Andrew-R** · 09-17-2010, 09:57 AM

Originally Posted by snb

It gives the result you asked for (if sheets(2) is empty)
It takes 2 pages to explain.
Please test it first in your application.

Originally Posted by snb

Avoid using VBA-code you do not understand.

Warning! Juxtaposition error!

**snb** · 09-17-2010, 10:01 AM

@Andrew,

So you used the code twice....(and sheets(2) wasn't empty)

with application.displayalerts=False it will run smoothly.
or

Please Login or Register  to view this content.

**Andrew-R** · 09-17-2010, 10:05 AM

I can assure you that for values of application = IRL my displayalerts has always been on False.

**coletteno1** · 09-17-2010, 10:07 AM

Thanks snb,
I get a 'type 13 runtime error- type mismatch' :S

**snb** · 09-17-2010, 10:09 AM

Did you copy my code ?
Did you alter the code ?
Are you using data different from the data in the sample you put here ?

**coletteno1** · 09-17-2010, 10:18 AM

Yes I am using different data, yes I copied your code and no I didn't alter it? I tried it on the sample code and it didn’t work either.

I'm really happy with the code that StephenR and Andrew-R produced, I was just looking for a way to be able to ignore certain applications from the output and I don't know enough VBA to alter the code to do this so any help would be greatly appreciated!
Colette

**StephenR** · 09-17-2010, 10:22 AM

That is very neat code snb, but it leaves off FISH for some reason when I ran it. It would help if you added sheets references everywhere too I think.

Modify loop...

Please Login or Register  to view this content.

**snb** · 09-17-2010, 11:18 AM

There's an strange error caused by application.transpose.
It can be circumvented by

Please Login or Register  to view this content.

The error is caused due to writing more than 255 characters in a cell.

**coletteno1** · 09-19-2010, 07:30 AM

any ideas guys?
Want to extend the code to ignore specified values such as 'security update'.... how would I do this?
Colette

**snb** · 09-19-2010, 11:37 AM

I improved my code in the last post. Did you test it ?

**coletteno1** · 09-19-2010, 01:02 PM

Hi snb, it worked on the example but when I tried it on a larger data set I got this error:
Run time 13 error- Type mismatch.
Is this because of the same issue i had earlier in the thread as in the example applications are called 'Application 1' etc whereas in the spreadsheet i'm implementing it on has values such as 'McAfee' and 'Apache Tomcat'?
Also where in this formula could I specify which applications to ignore?

**snb** · 09-19-2010, 03:32 PM

if you want to exclude 'McAfee'

Please Login or Register  to view this content.

If you do not provide some real data, we can't track down the error.
The only explanation appearing in my crystal ball is that not a single cell contains the string "System information for \\"

**Marcol** · 09-20-2010, 06:20 AM

How about a method that only uses formulae?

1/. Begin your data in A2 and add this line to the end of your raw data.

Please Login or Register  to view this content.

2/. Drag the formula in Columns C&D down to this added line.
In C2

Please Login or Register  to view this content.

In D2

Please Login or Register  to view this content.

3/. Filter C:D on Column D for non-blanks, at this stage any unwanted applications can also be filtered out.

4/. Copy the result and paste special > values to F:G

5/. Filter F:G on Column F for non-blanks

6/. Copy the result in Column G to Column I

7/. Remove the filter

8/. The result is now in Sheet2

With Sheet2
In A4

Please Login or Register  to view this content.

Drag/Fill down until "END" appears.

In B4

Please Login or Register  to view this content.

Drag/Fill down and across to suit.

Once this sheet is established it becomes a "live" template for any list you choose to import and filter.

Hope this helps, or at least offers a different view of your problem.

Separating Data

LinkBack

Thread Tools

Rate This Thread

Display

Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Re: Separating Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions