Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2


Your Help is greatly appreciated.




--
Regards,
Mahesh

On Tue, 20 Mar 2007 12:40:15 -0700, Mudragaddam
wrote:

Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2

Try this:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range

For Each rCell In Sheet1.Range("B2:B5").Cells
Set rFound = Sheet2.Rows(1).Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then 'header exists
Sheet2.Cells(Sheet2.Rows.Count,
rFound.Column).End(xlUp).Offset(1, 0).Value = _
rCell.Offset(0, -1).Value
Else 'header doesn't exist
With Sheet2.Cells(1,
Sheet2.Columns.Count).End(xlToLeft).Offset(0, 1)
.Value = rCell.Value
.Offset(1, 0).Value = rCell.Offset(0, -1).Value
End With
End If
Next rCell

End Sub

Make sure your sheet's are codenamed Sheet1 and Sheet2. Watch for word wrap
in this message.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Its exactly what i wanted. Thanks you very much.

Now have two more things to take care of :

1.Run Macro automatically whenever the sheet is updated with new severs
2. no duplication of data on sheet2

I will greatly appreciate if you can help me out with this.

I will be researching meanwhile. Thanks Again.

--
Regards,
Mahesh


"Dick Kusleika" wrote:

On Tue, 20 Mar 2007 12:40:15 -0700, Mudragaddam
wrote:

Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2

Try this:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range

For Each rCell In Sheet1.Range("B2:B5").Cells
Set rFound = Sheet2.Rows(1).Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then 'header exists
Sheet2.Cells(Sheet2.Rows.Count,
rFound.Column).End(xlUp).Offset(1, 0).Value = _
rCell.Offset(0, -1).Value
Else 'header doesn't exist
With Sheet2.Cells(1,
Sheet2.Columns.Count).End(xlToLeft).Offset(0, 1)
.Value = rCell.Value
.Offset(1, 0).Value = rCell.Offset(0, -1).Value
End With
End If
Next rCell

End Sub

Make sure your sheet's are codenamed Sheet1 and Sheet2. Watch for word wrap
in this message.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Wed, 21 Mar 2007 08:50:36 -0700, Mudragaddam
wrote:

Its exactly what i wanted. Thanks you very much.

Now have two more things to take care of :

1.Run Macro automatically whenever the sheet is updated with new severs
2. no duplication of data on sheet2

I will greatly appreciate if you can help me out with this.

I will be researching meanwhile. Thanks Again.

You could delete everything on Sheet2 and run the macro whenever something
changes on Sheet1. You would be recreating the list every time, but
depending on the size of the list, that may be acceptable to you. If you
use the Worksheet_Change event, the macro will run every time you change any
cell on the sheet. You could limit that to certain columns though.

For instance, you could limit it to only columns A and B, then make sure
there's a Server for every Environment before the macro runs.

Think about that and I can help you with the code when you get the logistics
worked out.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

There is several other data on the sheet that needs to be in place. these are
the columns in a particular data set. if it was possible to clear the data
under dev/int/preprod/prod everytime this macro is run to rewrite the list
again that would be the right direction for me (like an cleanup-verification
process).

And as you said implementing the worksheet_change for every update on sheet1
will make it perfectly automatic to update Sheet2 all the time.

I will greatly appreciate if you could put something as part of code to make
me understand better.

Thanks for responding.............

--
Regards,
Mahesh

On Wed, 21 Mar 2007 16:40:33 -0700, Mudragaddam
wrote:

There is several other data on the sheet that needs to be in place. these are
the columns in a particular data set. if it was possible to clear the data
under dev/int/preprod/prod everytime this macro is run to rewrite the list
again that would be the right direction for me (like an cleanup-verification
process).

And as you said implementing the worksheet_change for every update on sheet1
will make it perfectly automatic to update Sheet2 all the time.

I will greatly appreciate if you could put something as part of code to make
me understand better.


Put this in the worksheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Or Target.Column = 2 Then
If Not (IsEmpty(Me.Cells(Target.Row, 1).Value) And _
IsEmpty(Me.Cells(Target.Row, 1).Value)) Then

ListServers

End If
End If

End Sub

And change the other macro thusly:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range
Dim rRng As Range

Sheet2.Range("B1").CurrentRegion.ClearContents
Set rRng = Sheet1.Range("B2", Range("B65536").End(xlUp))

For Each rCell In rRng.Cells
Set rFound = Sheet2.Rows(1).Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then 'header exists
Sheet2.Cells(Sheet2.Rows.Count,
rFound.Column).End(xlUp).Offset(1, 0).Value = _
rCell.Offset(0, -1).Value
Else 'header doesn't exist
With Sheet2.Cells(1,
Sheet2.Columns.Count).End(xlToLeft).Offset(0, 1)
.Value = rCell.Value
.Offset(1, 0).Value = rCell.Offset(0, -1).Value
End With
End If
Next rCell

End Sub

You may still need to tweak this a bit, but it should get you started.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com