Help creating a report from a data table
Hi all. Been trying for a while to use pivots to make this happen but no
luck... hope you can help...
I receive data in an excel file that looks like this:
Col. A Has a customer name
Col. B Has the customer total Sales Amount
Col. C Has the Sales location # 1 - Name
Col. D Has the Sales Location # 1 - ID
Col. E Has the Sales Location # 1 - Amount
Col. F Has the Sales location # 2 - Name
Col. G Has the Sales Location # 2 - ID
Col. H Has the Sales Location # 2 - Amount
Col. I Has the Sales location # 3 - Name
Col. J Has the Sales Location # 3 - ID
Col. K Has the Sales Location # 3 - Amount
Note that Col. E, H & K sum up to col. B for each row in the file.
There are about 50 rows in each file with such data for various customers.
I would like to display the data in this format:
Col. A-------------Col. B--------------------Col. C---------------Col. D
Cust. Name
SMITH------------ Location # 1 Name --- Location # 1 ID --- Location # 1
Amount
SMITH-------------Location # 2 Name --- Location # 2 ID --- Location # 2
Amount
SMITH-------------Location # 3 Name --- Location # 3 ID --- Location # 3
Amount
PETER------------ Location # 1 Name --- Location # 1 ID --- Location # 1
Amount
PETER-------------Location # 2 Name --- Location # 2 ID --- Location # 2
Amount
PETER-------------Location # 3 Name --- Location # 3 ID --- Location # 3
Amount
WILLIAM---------- Location # 1 Name --- Location # 1 ID --- Location # 1
Amount
WILLIAM-----------Location # 2 Name --- Location # 2 ID --- Location # 2
Amount
WILLIAM ----------Location # 3 Name --- Location # 3 ID --- Location # 3
Amount
I would appreciate any help you can provide.
Regards,
sk
|