formatting a company chart

H

harlequin

New member
Joined
Aug 18, 2011
Messages
5
Reaction score
0
Points
0
Hi guys,

new here--I'm not the excel guy in our office, but have been asked to do a quick bit of formatting to a worksheet for viewing purposes. I know what I want to accomplish, and I hope there's a formula that can do it--the worksheet is much too long to organize manually. Here's the structure I'm starting with:

Screenshot2011-08-18at71406AM.png


Here's what I want to do:

Screenshot2011-08-18at71437AM.png


then, once I've put the two side-by-side, I can eliminate the redundant column like this:

Screenshot2011-08-18at71644AM.png


My boss will be looking for an answer in a few hours, please help me! any and all tips are appreciated. Thank you very much guys!
 
Try

Code: 
Public Sub ProcessData()
Dim Lastrow As Long
Dim MatchRow As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        .Columns("F:H").Insert
        Lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        .Range("K2:L2").Copy .Range("F2")
        For i = 3 To Lastrow
        
            MatchRow = 0
            On Error Resume Next
            MatchRow = Application.Match(.Cells(i, "J").Value, .Columns("E"), 0)
            On Error GoTo 0
            If MatchRow > 0 Then
            
                .Cells(i, "K").Resize(, 2).Copy .Cells(MatchRow, "F")
            End If
        Next i
        
        .Columns("J:L").Delete
    End With
    
    Application.ScreenUpdating = True
End Sub
 
hey thanks for the quick reply! gotta ask a REAL newbie question though :doh: where can I enter code in excel mac 2008? I also have excel mac 2011 on my work computer.

I know how to work with code in general, so what you gave me isn't foreign, i just don't know where to enter it in excel, never tried before
 
Excel Mac 2008 doesn't support VBA does it? I think it was taken out then put back in 2011.

Just add it into a standard code module in the VBIDE.
 
PERFECT! thank you very much, I just applied it to the larger worksheet. with only a few adjustments to the code to get it to reference the correct cells/columns, it worked like a charm. :dance::hail: thanks bob
 
You must log in or register to reply here.
Back
Top