Building form based off of data sheet

[gngriffis]

Sheet1 contains the data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Position[/TD]
[TD]Name[/TD]
[TD]Extension[/TD]
[/TR]
</tbody>[/TABLE]

What I would like on sheet2 is
Row 1 = 1 merged Column with Department Value Centered
Row 2 = Column 1 Position, Column 2 Name, Column 3 Extension

Repeat while Department Value is the same, when the Department Value changes Merge the Column and insert new Department Name, Next row start adding again

Is that even possible?

 

[RobertSF]

Hey, welcome to Mr. Excel. What you want is simple in concept but, as far as I can see, can only be done either by hand or with VBA code. You can't really use formulas for this. I'm at work right now, but I'll see if I can put some code together afterwards.

 

[RobertSF]

This code should do it. I've spelled everything out without abbreviating anything, so it should be clear, but feel free to ask any questions.

Option Explicit

Sub CreateRoster()
    
    Const firstRow = 2
    
    Dim source As Range
    Set source = ThisWorkbook.Sheets("Sheet1").UsedRange
    
    Set source = source.Offset(1).Resize(source.rows.Count - 1)
    
    Dim SRow As Long
    Dim DRow As Long
    Dim dept As String
    Dim dest As Range
    
    'prime the pump with the first department
    DRow = 1
    Set dest = ThisWorkbook.Sheets("Sheet2").Cells
    dept = source.rows(firstRow).Cells(1).Value
    dest.rows(DRow).Range(dest.rows(DRow).Cells(1, 1), dest.rows(DRow).Cells(1, 3)).MergeCells = True
    dest.rows(DRow).Cells(1).Value = dept
    
    For SRow = firstRow To source.rows.Count
        'we need two row variables because they don't move together since
        'we are creating a whole row for the department
        DRow = DRow + 1
        dest.rows(DRow).Cells(1).Value = source.rows(SRow).Cells(2).Value
        dest.rows(DRow).Cells(2).Value = source.rows(SRow).Cells(3).Value
        dest.rows(DRow).Cells(3).Value = source.rows(SRow).Cells(4).Value
        'does the next row have a different department?
        If source.rows(SRow + 1).Cells(1).Value <> dept Then
            'yes, so prepare for the next department
            dept = source.rows(SRow + 1).Cells(1).Value
            DRow = DRow + 1
            dest.rows(DRow).Range(dest.rows(DRow).Cells(1, 1), dest.rows(DRow).Cells(1, 3)).MergeCells = True
            dest.rows(DRow).Cells(1).Value = dept
        End If
    Next SRow
    
End Sub

 

[gngriffis]

Thats excellent! Thank you Robert!

 

[gngriffis]

Robert,
Thank you again for the code! One thing I have found is that when the Department changes, the merge takes place incorrectly. Ive played with it for awhile and it appears that the merge row is 'adding' the SRow and DRow values and merging the subsequent row? i.e. If the correct DRow is 6 and the current SRow is 5, Row 11 is merged. I tested this by removing an entry and found the same result. Its a bit confusing in that I set up cells to display the SRow and DRow values as I ran through the script and they are correct values.

dest.Rows(DRow).Range(dest.Rows(DRow).Cells(1, 1), dest.Rows(DRow).Cells(1, 3)).MergeCells = True

 

[RobertSF]

You're very welcome! Let me give it a look and get back to you. Meanwhile, have you used the VBA Debugger? You took some good trouble-shooting steps, so maybe the debugger will be interesting!

There are several ways to do it, but the easiest is to place the cursor within the code for the sub that you want to run and press F8. The first line of the sub will highlight and there will be an arrow pointing to it. Every time you press F8, the arrow moves forward to the next line, and the computer executes that line of code. To watch a variable change, right click on the variable and select Watch from the drop-down menu.

You can also press Control-G to get an "immediate" window. You can type code in this window and it will work. For example, you can type "print SRow" and it will print the value of SRow. You can also abbreviate the print command to just a question mark, like ?SRow.

That's the basics. Here's a pretty good tutorial. https://www.techonthenet.com/excel/macros/vba_debug2013.php

 

[gngriffis]

As a quick measure, I rem'd 'dest.Rows(DRow).Range(dest.Rows(DRow).Cells(1, 1), dest.Rows(DRow).Cells(1, 3)).MergeCells = True and inserted Range("A" & DRow & ":" & "C" & DRow).Merge 'added code.
I wasnt aware of the Control-G command - Thank you!