Header / Detail lines in excel report - Need to add column in detail with value from header line

[dlwallace]

Source:

Producer	Ralph
Ind  Type	Customer Name	Coverage  To	Premium Period	Commission
FY	MICHAEL		07/01/2019	$47.27
FY	BAO	05/01/2019	05/01/2019	($67.78)
FY	BAO	05/01/2019	06/01/2019	($67.78)
Producer	Bob
Ind  Type	Customer Name	Coverage  To	Premium Period	Commission
FY	FRED		07/01/2019	$51.66
FY	BARBARA		07/01/2019	$38.62
FY	WENDY		07/01/2019	$189.14

Desired result:

Ind  Type	Customer Name	Coverage  To	Premium Period	Commission	Producer
FY	MICHAEL		07/01/2019	$47.27	Ralph
FY	BAO	05/01/2019	05/01/2019	($67.78)	Ralph
FY	BAO	05/01/2019	06/01/2019	($67.78)	Ralph
FY	FRED		07/01/2019	$51.66	Bob
FY	BARBARA		07/01/2019	$38.62	Bob
FY	WENDY		07/01/2019	$189.14	Bob

Any help would be appreciated.

Dan

[ImkeF · Accepted solution]

Hi @dlwallace  ,

the following steps should get you there:

1.  Add a conditional column like so: 
2.  Check the new column and fill down values (this will add the name of the producer into every row. The following steps will just clean up the table. 
3.  Filter out blanks on Column 4
4.  Promote Headers
5.  Delete / filter out rows in column "Customer Name" with value "Customer Name"

[dlwallace]

Thanks!  So simple once you know how.  I appreciate the help.