Excel String Processing

[Schmuck]

Hi all,

I was wondering if you could help me with what may very well be a
couple of simple problems. I am not a regular Excel user, so please
excuse any obvious mistakes.

I have a list of resources (actually disks) which can be allocated to
any of 5 servers. On each machine I have a script that outputs all the
disk details... physical disk number and name, logical disk group and
name... for each disk that currently belongs to each server. Disks
beloning to another machine are known as "foreign" and are presented
as (diskgroup) .. ie with parenthesis. The diskname of a foreign disk
is not visible.

I need to combine these outputs into one table that shows how all the
disks are allocated along with their physical disk name and logical
details. The physical disk number is fixed across all machines, and
thus is my index column.

For example (My Input.. proportional font.. sorry):

Server "A":
Physical Logical
Number Name Group Name
20 disk20 appA appA01
21 disk21 appA appA02
22 disk22 (dbase) -
23 disk23 (dbase) -
24 disk24 (mp3) -

Server "B":
Physical Logical
Number Name Group Name
20 disk120 (appA) -
21 disk121 (appA) -
22 disk122 dbase dbase01
23 disk123 dbase dbase02
24 disk124 dmp3 mp301


The output I need is:

Phys. Server Phys. Logical
Number Name Group Name
20 A disk20 appA appA01
21 A disk21 appA appA02
22 B disk122 dbase dbase01
23 B disk123 dbase dbase02
24 B disk124 dmp3 grp301

Its obviously more complicated that this... there are actually half a
dozen servers and serveral hundred disks. At the moment I have been
able to fill in the server column using a nested "if".. which is
obviouly ugly.

Is there a better way?

Thanks in advance

Matt

 

[Bernie Deitrick]

Matt,

Try the macro below on a copy of your data. The macro assumes:

All your lists are on the activesheet, starting in Row1.
The data values are in separate cells - you may need to use Data | Text to
Columns.... to get it that way initially.
The cells with the ()'s are in column C.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(ISERROR(FIND(""("",RC[3],1)),""Keep"",""Trash"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = _
"=MID(RC[-1],9,1)"
Range("B2").FormulaR1C1 = _
"=IF(LEFT(RC[-1],6)=""Server"",MID(RC[-1],9,1),R[-1]C)"
Range("B2").AutoFill Destination:=Range("B2:B" & _
ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefault
With Range("B:B")
.Copy
.PasteSpecial Paste:=xlValues
End With
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub