Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code

[ScotSquad]

Hi all,

I have a simplified version of a sheet I'm working with below showing the data as it is, and how I'd like it to be....
CURRENTLY...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Blank Values in Hierarchy
[/TD]
[TD]Line 1 Manager
[/TD]
[TD]Line 2 Manager
[/TD]
[TD]Line 3 Manager
[/TD]
[TD]Line 4 Manager
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]2
[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]3
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]1
[/TD]
[TD]Manager C
[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 4
[/TD]
[TD]2
[/TD]
[TD]Manager D
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 5
[/TD]
[TD]3
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

HOW I'D LIKE IT TO BE...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Blank Values in Hierarchy
[/TD]
[TD]Line 1 Manager
[/TD]
[TD]Line 2 Manager
[/TD]
[TD]Line 3 Manager
[/TD]
[TD]Line 4 Manager
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Manager C[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 4
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager D
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 5
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager A[/TD]
[/TR]
</tbody>[/TABLE]

Essentially what I'm trying to do is "right-align" the manager hierarchy by moving the hierarchy values to the right depending on the amount of blank values (which I've got a count of for each row in column B). For example, in row 2 (Employee 1) I want to insert 2 cells at C2 to move the hierarchy along by 2. However in row 4 (Employee 3), I'd only want to insert 1 cell at C4 as the data only needs to move along by 1 cell as there is only 1 blank value etc. etc.

Sadly it's not possible to do this manually as I have a sheet with over 1,000 rows so I'm hoping that there is a quick VBA solution that could go through each row by row, checking the value in column B, and inserting the appropriate number of cells at C2, C3, C4 etc. in order to move the data along to "right align" it if that makes sense.

I'd appreciate any help or solution that could be provided, or if anyone can think of a more elegant solution I'd love to hear it!

Thanks in advance!

 

[gallen]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Hi all,

Sadly it's not possible to do this manually as I have a sheet with over 1,000 rows so I'm hoping that there is a quick VBA solution that could go through each row by row, checking the value in column B, and inserting the appropriate number of cells at C2, C3, C4 etc. in order to move the data along to "right align" it if that makes sense.

Thanks in advance!

Looking at your before and after I don't see how inserting cells (you can only insert rows or columns) is needed. You just need to move the text to the cell on the right if it is blank. Is this what you need?

Edit: is your table always 6 columns across or does the code need to be dynamic?

 

[MARK858]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Try...

Sub InsCells()
    Dim i As Long, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lr
        Cells(i, 3).Resize(, Cells(i, 2).Value).Insert Shift:=xlToRight
    Next
End Sub

 

[gallen]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

This works but I know it can be much 'neater' I tried yours mark but it errored out.

A neater way would be just to select all managers and 'move them' number of column shown in col B but this does work

Sub RightAlign()
    Dim lr As Long, i As Long, x As Long, y As Long
    Dim iBlanks As Integer
    Dim rRightManager As Range 'rightmost manager
    
    'Get last row
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    'loop through all rows
    For i = 2 To lr
        'Get number of blanks
        iBlanks = Range("B" & i).Value
        
        'Get The rightmost manager
        Do Until Range("C" & i).Offset(0, x) = ""
            x = x + 1
        Loop
        Set rRightManager = Range("C" & i).Offset(0, x - 1) 'Rightmost manager will be x-1
        
        'Starting with right most manager move managers across
        For y = rRightManager.Column To 3 Step -1
            Cells(i, y).Offset(0, iBlanks) = Cells(i, y)
            Cells(i, y) = ""
        Next y
        
    Next i
    
End Sub

 

[MARK858]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I tried yours mark but it errored out

The only time the code errors out on me is if there is a blank or no value in column B as you can't resize by zero and the OP's post shows that they already have the number of blanks in column B.

depending on the amount of blank values (which I've got a count of for each row in column B).

Another way...

Sub InsCells2()
    Dim i As Long, lr As Long, lc As Long, x As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Copy
        Cells(i, 3 + Cells(i, 2).Value).PasteSpecial xlPasteValues
        Cells(i, 3).Resize(1, Cells(i, 2).Value).ClearContents
    Next
End Sub

Based on the ranges and info below (as was the first code I posted).

Excel 2010
	A	B	C	D	E	F
1	Employee	Blank Values in Hierarchy	Line 1 Manager	Line 2 Manager	Line 3 Manager	Line 4 Manager
2	Employee 1	2	Manager B	Manager A
3	Employee 2	3	Manager A
4	Employee 3	1	Manager C	Manager B	Manager A
5	Employee 4	2	Manager D	Manager A
6	Employee 5	3	Manager A
Sheet1

 

[MARK858]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Just for completeness, a cut method that doesn't error out if a zero or blank in column B...

Sub InsCells()
    Dim i As Long, lr As Long, lc As Long, x As Long

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column

    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        If Cells(i, 2) <> "" And Cells(i, 2) <> 0 Then
            Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Cut Cells(i, 3 + Cells(i, 2).Value)
        End If
    Next
End Sub

and the same for the paste version...

Sub InsCells2()
    Dim i As Long, lr As Long, lc As Long, x As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        If Cells(i, 2) <> "" And Cells(i, 2) <> 0 Then
            Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Copy
            Cells(i, 3 + Cells(i, 2).Value).PasteSpecial xlPasteValues
            Cells(i, 3).Resize(1, Cells(i, 2).Value).ClearContents
        End If
    Next
End Sub

 

[gallen]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I tried the cut, but couldn't get it to paste values only so it removed all formatting too.

This is a condensed version of my first post too (yes it's quiet at work)

Sub RightAlign()
Dim i As Long, x As Long, y As Long
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Do Until Range("C" & i).Offset(0, x) = ""
            x = x + 1
        Loop
        For y = Range("C" & i).Offset(0, x - 1).Column To 3 Step -1
            Cells(i, y).Offset(0, Cells(i, 2)) = Cells(i, y)
            Cells(i, y) = ""
        Next y
    Next i
End Sub

 

[MARK858]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I tried the cut, but couldn't get it to paste values only

You can't cut and paste values, it's not how cut works.

 

[ScotSquad]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Hi guys,

Thanks for the help so far.

Rather than copying and pasting the values (I've tried copying and pasting values and had various issues with this as the number of "manager" columns varies!).

I was hoping that it would be possible to insert/shift right cells row-by-row (with the number of cells inserted/shifted based on the value in the "Blank Values in Hierarchy" cell, I can do this manually so I assumed there could be a quick macro/vba script that could do this for the other 999 records!!

Thanks

 

[gallen]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I don't think the number of columns matters in either code.

Mine should work no matter how many columns there are though would error if there is a blank cell between 1 manager and the next