Results 1 to 5 of 5

Thread: formatting a company chart

  1. #1

    formatting a company chart



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:



    Here's what I want to do:



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



    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!

  2. #2
    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

  3. #3
    hey thanks for the quick reply! gotta ask a REAL newbie question though 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

  4. #4
    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.

  5. #5
    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. thanks bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •