Formatting raw data with extra rows automatically added in-between records

[Withents]

My company has an accounting software which spits out ugly reports. Traditionally, they have been exporting the raw data and then formatting everything manually. This particular report is listing payroll information where column A lists the employee, column B lists the project, column C lists the hours that were worked by that person on that project (there are a few other columns which list other information for that record, but you get the idea). The raw data is output with one header row at the top of all of the data. The company wants there to be essentially three rows in between each employee. A line for totals, a blank line that's filled with black (to mark the end of the employees record), and a line for the header row before the start of the next employee.

How on earth can I make it so that I can import the raw data and get the rows to generate between each employee without having to do it manually copy/paste? I thought about having the raw data on another tab and having a set of lookup functions in a formatted tab which would pull the info in, but each person has several different project lines they put time into. It can also change how many projects they are working on from pay period to pay period. Everyone has varying amounts of projects they are working on (some people have one and others have five).

Any advice or just generally pointing me in a direction would be helpful and much appreciated! TIA

geometry dash lite​

 

[Peter_SSs]

Welcome to the MrExcel board!

You have not given us much about exactly what has to be totalled but see if something like this might be useful.

Sub ReFormat()
  Dim cols As Long, r As Long
  Dim Hdrs As Range, rA As Range
  
  cols = Cells(1, Columns.Count).End(xlToLeft).Column
  Set Hdrs = Range("A1").Resize(, cols)
  For r = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 2 Step -1
    If Cells(r, 1).Value <> Cells(r + 1, 1).Value Then
      Rows(r + 1).Resize(3).Insert
      Hdrs.Copy Destination:=Cells(r + 3, 1)
      Cells(r + 2, 1).Resize(, cols).Interior.Color = vbBlack
    End If
  Next r
  For Each rA In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Count + 1).Formula2 = "=SUM(" & rA.Address & ")"
    Cells(rA.Row + rA.Count, "A").Value = "Total"
  Next rA
End Sub

My sample data before:

Withents.xlsm
	A	B	C	D	E
1	Employee	Data 1	Data 2	Value	Data 4
2	Name 1	data	data	3	data
3	Name 1	data	data	2	data
4	Name 1	data	data	5	data
5	Name 2	data	data	6	data
6	Name 3	data	data	1	data
7	Name 3	data	data	2	data
8	Name 3	data	data	4	data
9	Name 3	data	data	8	data
10	Name 3	data	data	7	data
11	Name 4	data	data	1	data
12	Name 4	data	data	2	data
Sheet1

.. and after:

Withents.xlsm
	A	B	C	D	E
1	Employee	Data 1	Data 2	Value	Data 4
2	Name 1	data	data	3	data
3	Name 1	data	data	2	data
4	Name 1	data	data	5	data
5	Total			10
6
7	Employee	Data 1	Data 2	Value	Data 4
8	Name 2	data	data	6	data
9	Total			6
10
11	Employee	Data 1	Data 2	Value	Data 4
12	Name 3	data	data	1	data
13	Name 3	data	data	2	data
14	Name 3	data	data	4	data
15	Name 3	data	data	8	data
16	Name 3	data	data	7	data
17	Total			22
18
19	Employee	Data 1	Data 2	Value	Data 4
20	Name 4	data	data	1	data
21	Name 4	data	data	2	data
22	Total			3
Sheet1

Cell Formulas
Range	Formula
D5	D5	=SUM($D$2:$D$4)
D9	D9	=SUM($D$8)
D17	D17	=SUM($D$12:$D$16)
D22	D22	=SUM($D$20:$D$21)