I desparately need help with Excel!!

[Dominus]

I have this Excel spreadsheet to organize, it contains all the hardware/network/insurance info for all our schools computers. It's something that our school has been trying to put together for quite some time, and I have several drafts to work with.

I have a nice bit done, all the IP, hostnames, MAC addresses, shares, most of the locations and some other info, but I have come to a problem when it comes to organizing the hardware info, namely CPUs and RAM.

The felloow that catalogued all the hardware info didn't seemto have any Excel experience whatsoever, and organized it in such a bad way that it is nearly useless to me. I can't use any of my limited Excel expertise to organize it.

What I would greatly appreciate is someone who perhaps does this for a lving to give me some tips or pointers on making really advanced formulae, or point me in the direction of some good online tutorials.

Not expecting someone to do the work for me, but I need some help in this.

[tonym]

Dominus,

What are you trying to do? Sort stuff or do calculations or...?????


Tony

[Dominus]

Sort out some stuff.

One problem is sorting IPs.

When I try to sort in ascending order, the IPs go like this:

123.123.123.1
123.123.123.10
123.123.123.100
123.123.123.101
123.123.123.102
and etc.

Do you know of a way where I could sort them according to the last number? Sorry, I'm not that knowledgible of spreadsheeting.

[This message has been edited by Dominus (edited 03-09-2000).]

[Roraycr]

If the last node is in a column by itself, it will sort the way you want.

[nk4]

Not that this is definately the only way, but it should be possible to do it with macros. Excell has Visual Basic for applications integrated in it. And the help has a decent coverage of the language. If you have the time to spend you might find a solution there.

[U-96]

I sounds like Access could help you more than Excel in this instance...

This would allow you to have separate and discrete tables for each category of information you wish to store: eg

Computer (location, IP, role, network, warranty, maintenence record)
Components (type, peripherals, manufacturer, warranty etc)
Contacts (e.g. suppliers, engineers, etc for each component)

all these can be linked but remain independent of each other.

I imagine your current dilemma is that you want to add multiple components to a single item in your spreadsheet, without adding several more fields that may or may not be used, or repeating information that appears elsewhere? I'm sure there are ways of linking worksheets in Excel that can work like table structures in Access, but as Access is designed for it, you might want to consider the change. Don't worry about all your data, it shouldn't be a problem to import it http://www.sysopt.com/forum/smile.gif

If you have Access available, play around with the database wizard and see if there are any models there that offer some of what you want.

U-96

[edit] oh well, new stuff was added as I type. Try this:


Sorting IP addresses using arrays
>Hi --
> I wrote a quick/dirty VBA procedure to sort a customer's work-
>sheet, but feel like it's a pretty kludgey approach. I wonder if
>there's a more elegant approach. One of their columns contains IP
>addresses (in the form x.x.x.x) and the real problem is that each x
>can be one, two, or three digits. If they use the normal Excel
>sort, they get IP addresses of .1, followed by .10 thru .19, followed
>by .2, and so on. In a more general sense, this problem applies to
>any field that contains mixed text and numeric (where the numeric
>portion is not left zero-filled). For example, part numbers like
>ITEM1...ITEM10...ITEM100 or version numbers like 2.1.1 thru 2.10.9,
>or any number of other examples. Although it would be nice to be
>able to tell them that they MUST always left zero-fill, I think it's
>up to me to make the computer conform to the human rather than the
>other way around.

You can perform the sorting operation you describe at a much higher
level of performance if you do all of your sorting in a VBA array, as
opposed to using For-Each-Next loops with cells on a worksheet. I've
re-written your original routine to do this - I believe you will find
it to be faster:


Option Base 1
Option Explicit
Option Compare Text

Sub SortIP()
Dim Array1 As Variant
Dim Array2() As Variant
Dim ArraySize As Integer
Dim x As Integer
Dim y As Variant
Dim Dot1 As Integer, Dot2 As Integer, Dot3 As Integer
Dim IP1 As String, IP2 As String, IP3 As String, IP4 As String
Dim IPCell As Range
Worksheets("Sheet1").Activate
Array1 = Range("A1").CurrentRegion.Columns(1).Value
ArraySize = UBound(Array1, 1)
ReDim Array2(ArraySize, ArraySize)
For x = 1 To ArraySize
Array2(x, 1) = Array1(x, 1)
Dot1 = InStr(1, Array1(x, 1), ".")
Dot2 = InStr(Dot1 + 1, Array1(x, 1), ".")
Dot3 = InStr(Dot2 + 1, Array1(x, 1), ".")
IP1 = Format(Mid(Array1(x, 1), 1, Dot1 - 1), "000")
IP2 = Format(Mid(Array1(x, 1), Dot1 + 1, Dot2 - Dot1 - 1), "000")
IP3 = Format(Mid(Array1(x, 1), Dot2 + 1, Dot3 - Dot2 - 1), "000")
IP4 = Format(Mid(Array1(x, 1), Dot3 + 1, 3), "000")
Array2(x, 2) = IP1 & IP2 & IP3 & IP4
Next
Array1 = SortArray(Array2)
Range("A1").CurrentRegion.Columns(1).Value = Application.Transpose(Array1)
End Sub

Function SortArray(ByRef Array2)
Dim Changed As Boolean
Dim Temp1 As Variant
Dim Temp2 As Variant
Dim x As Integer
Dim z As Variant
Dim Array3() As Variant
ReDim Array3(UBound(Array2, 2))
Changed = True
Do While Changed
Changed = False
For x = 1 To UBound(Array2, 2) - 1
If StrComp(Array2(x, 2), Array2(x + 1, 2), 1) = 1 Then
Temp1 = Array2(x + 1, 1)
Temp2 = Array2(x + 1, 2)
Array2(x + 1, 1) = Array2(x, 1)
Array2(x + 1, 2) = Array2(x, 2)
Array2(x, 1) = Temp1
Array2(x, 2) = Temp2
Changed = True
End If
Next
Loop
For x = 1 To UBound(Array2, 2)
Array3(x) = Array2(x, 1)
Next
SortArray = Array3
End Function

Eric Wells
Microsoft



from http://www.vex.net/~negandhi/excel/arrays.phtml

[This message has been edited by U-96 (edited 03-09-2000).]

[chas3rd]

Tho I use quattro pro Im thinking you can use a diff field for each set Of #'s in the IP prob not much help but its easiest thing off the top of my head

"when in doubt I whip it out" Ted Nugent

[tonym]

If you make the IP address four columns then you can sort by any portion of the address.

Also, the example you show does arrange the addresses in ascending order. What do you want the output to look like??

Tony

[tonym]

Dominus,

Send me a copy by e-mail and I'll fix it and tell you what I did at each place! But you won't get it back until next Mon. or Tues.!

Tony

[Dominus]

This spreadsheet is supposed to be passed in tomorrow, but thats not gonna happen. My teacher is a good guy, and *he* was supposed to do this, so he'll give me some slack.

We have a class C at school, so I can divide the IP section into 2 columns. That should get me moving again, though it won't solve all my troubles. I wish you could see what I have to work with here, because it is simply gross. About 130 systems on the WAN, and the data I have already is horribly organised. There's absolutely no pattern in it that I can take advantage of.

I just hope I don't have to resort to entering the data in again by hand.

[Dominus]

As appealing as that sounds, I'm afraid I can't. The spreadsheet contains info that you could possibly use to h@><0r our network, plus my teacher would skin me if I did.

I'm supposed to be getting credit for this, so therefore I actually want to do it.

[tonym]

I understand completely!!!

If you have any un-sensitive information that you can use help with, the offer still holds. I'm interested in helpin', not h###in'


Tony