Sort and Pivot data into a grid w/ Non-stacked Multi Column in Row Field

[AMHopkins]

Need:Show Name, Address 1, Address 2, City, State, ZIP and ID in Row Field on a single row by name using Pivot Table. Data is dynamic and output must update after data is changed.

I have a data array composed of multiple columns for Project, Company, Percentage Owned, Partner Name, Address 1, Address 2, City, State, ZIP and ID. Within this array a single partner may be listed multiple times; once for each company he owns an interest in.

It was easy to create a pivot table to show the Partners in the Rows field, the Company in the Columns field and Percentage in the Data field.

Data Table

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Project	Company	%	Name	Address 1	Add 2	City	ST	Zip	ID
2	P1	Alpha Co.	20	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123
3		Beta Co.	40	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123
4		Omega Co.	45	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123
5		Gama Co.	33	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123
6	P2	Kapa Co.	10	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123
7	P1	Alpha Co.	20	Jane Doe	1 Somewhere		USA	ZZ	12345	456
8		Beta Co.	40	Jane Doe	1 Somewhere		USA	ZZ	12345	456
9		Gama Co.	33	Jane Doe	1 Somewhere		USA	ZZ	12345	456
10		Mu Co.	40	Jane Doe	1 Somewhere		USA	ZZ	12345	456
11	P1	Alpha Co.	50	Bob Floater	0 Feet Deep Blvd		Big Pond	QQ	14785	789
12	P2	Gama Co.	34	Bob Floater	0 Feet Deep Blvd		Big Pond	QQ	14785	789
13		Kapa Co.	50	Bob Floater	0 Feet Deep Blvd		Big Pond	QQ	14785	789
14		Mu Co.	30	Bob Floater	0 Feet Deep Blvd		Big Pond	QQ	14785	789
15		Omega Co.	15	Bob Floater	0 Feet Deep Blvd		Big Pond	QQ	14785	789
16	P1	Alpha Co.	10	Holly Jolly	9 Deck Hall		North Pole	NP	15984	963
17		Beta Co.	20	Holly Jolly	9 Deck Hall		North Pole	NP	15984	963
18	P2	Kapa Co.	40	Holly Jolly	9 Deck Hall		North Pole	NP	15984	963
19		Mu Co.	30	Holly Jolly	9 Deck Hall		North Pole	NP	15984	963
20		Omega Co.	40	Holly Jolly	9 Deck Hall		North Pole	NP	15984	963
Data

Excel 2003/>

Desired Output:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Project							(All)
2
3	Sum of %							Company
4	Name	Address 1	Add 2	City	ST	Zip	ID	Alpha Co.	Beta Co.	Gama Co.	Kapa Co.	Mu Co.	Omega Co.
5	Bob Floater	0 Feet Deep Blvd	-	Big Pond	QQ	14785	789	50		34	50	30	15
6	Holly Jolly	9 Deck Hall	-	North Pole	NP	15984	963	10	20		40	30	40
7	Jane Doe	1 Somewhere	-	USA	ZZ	12345	456	20	40	33		40
8	John Smith	0 Main Street	Apt #1	Small Town	ST	98765	123	20	40	33	10		45
9	Grand Total							100	100	100	100	100	100
Sheet2

Excel 2003

Any suggestions of alternate solutions acceptable.

 

[AMHopkins]

Solution:
Step 1) Create a "consolidation" column on the data page for the address. Write a formula to consolidate each part of the address into a single cell.

Step 2) Drag the new consolidation column to the row area of the pivot table, select Field settings for that field and select "None" under subtotals.

Step 3) Repeat Step 2 for the ID

This results in a single row showing: Column A, Name; Column B, Address; Column C, ID in the Row Fields of the table.

It will be at this point you will see any elements with multiple addresses or IDs.