ClosedXML Excel Template

[wgeohagan]

I am trying to create a simple report using a ClosedXML Excel Template. I have a .net application receiving data from SQL Server and then using that data to create the report in excel based on the template. I cannot figure out how to group the WorkNumbers so their Types are together. I have given example data and how I would like the report to look. Any help on grouping would be appreciated.

Thank You

 

[CSmith]

Pivot table should help you achieve the desired results.

 

[RudRud]

Book2
	A	B	C	D	E	F	G	H
1	Worknumber	Type	Numhours
2	214212	Direct	30		214212	Type	NumHours
3	214212	Indirect	8			Direct	30
4	214213	Direct	45			Indirect	8
5	214218	Direct	32
6	214218	Indirect	15		214213	Type	NumHours
7	214218	Overtime	12			Direct	45
8	214213	Indirect	8			Indirect	8
9
10					214218	Type	NumHours
11						Direct	32
12						Indirect	15
13						Overtime	12
14
Sheet2

Sub test()
    Dim dict As New Dictionary
    
    
    Range("a1:c" & Cells(Rows.Count, "a").End(xlUp).Row).Copy [e1]
    
     Range("e2:g8").Sort key1:=Range("e2"), order1:=xlAscending, key2:=Range("e2"), order2:=xlAscending
     
    a = Range("e2:g" & Cells(Rows.Count, "A").End(xlUp).Row)
    

   Range("e1:g" & Cells(Rows.Count, "g").End(xlUp).Row).Clear

    ReDim b(1 To 100, 1 To 7)
    
    For i = 1 To UBound(a, 1)
        If Not dict.Exists(a(i, 1)) Then
            R = R + IIf(R > 1, 2, 1)
            dict.Add a(i, 1), R
            b(R, 1) = a(i, 1)
            b(R, 2) = "Type"
            b(R, 3) = "NumHours"
        End If
        R = R + 1
        b(R, 2) = a(i, 2)
        b(R, 3) = a(i, 3)
    Next i

Range("e2").Resize(R, 3).Value = b
End Sub