Normalizing Data with Multiple Column Headers

jcastles003

I have a large database that keeps track of income data.  It has multiple headers for each column, and I am having difficulty figuring out how to normalize the data.

Below is an example of what input I receive.  The amounts presented represent income earned.  It needs to remain dynamic, because the number of entities (columns) and number of states/revenue types (rows) with amounts changes with each input received.

	Parent	Holdco 1	Holdco 1	Holdco 2
	Subsidiary	Sub 1	Sub 2	Sub 1
State	Revenue Type
AL	Inventory Sale	100	200	50
AL	Rental Income	150	300	50
MS	Inventory Sale	200	400	50
MS	Rental Income	250	500	50

Below represents the normalized data output that I would like.

Parent	Subsidiary	State	Revenue Type	Amount
Holdco 1	Sub 1	AL	Inventory Sale	100
Holdco 1	Sub 1	AL	Rental Income	150
Holdco 1	Sub 1	MS	Inventory Sale	200
Holdco 1	Sub 1	MS	Rental Income	250
Holdco 1	Sub 2	AL	Inventory Sale	200
Holdco 1	Sub 2	AL	Rental Income	300
Holdco 1	Sub 2	MS	Inventory Sale	400
Holdco 1	Sub 2	MS	Rental Income	500
Holdco 2	Sub 1	AL	Inventory Sale	50
Holdco 2	Sub 1	AL	Rental Income	50
Holdco 2	Sub 1	MS	Inventory Sale	50
Holdco 2	Sub 1	MS	Rental Income	50

I basically have no idea how to get it in that format and am completely lost.  I've been playing around with CrossTab, Summarize, etc, and cannot figure out a way to convert it.

RodL

Attached is an example of how you might approach this. I think the key is to separate out the headers and deal with them separately, and then union back in the actual data.

I'm doing this using the Select Records tool with the assumption that you always have the three rows that are your "headers". Another assumption is that the top two header rows are consistently named Parent and Subsidiary. But this should be dynamic from the standpoint of an unlimited number of Parent/Subsidiary combinations.

And finally I'm hard-coding the final column names.

Hope this gets you closer to a solution.

Multiple Header Lines Example.yxmd