Consolidation Columns to Horizontal Table

[rcb007]

I created a consolidation list in Columns J and K. I am trying to get the data to look like the horizontal table below.

For the ID column, I can consolidate the Column D to populate the values in O3. But I am not sure how to lookup the data.

Thank you for any direction on how to possible get this to work.

Parcel Storm Drainage Import Tool.xlsm
	D	E	F	G
1
2	2		2 - Coverage - Impervious	0.07
3	2		2 - Coverage - Pervious	0.47
4	2		2 - Coverage - Pervious	0.03
5	2		2 - Coverage - Pervious	0.01
6	2		2 - Coverage - Pervious	0.00
7	2		2 - Coverage - Pervious	0.00
8	2		2 - Coverage - Pervious	0.01
9	2		2 - Coverage - Pervious	0.01
10	2		2 - Coverage - Pervious	0.00
11	2		2 - Coverage - Pervious	0.01
12	2		2 - Coverage - Impervious	0.01
13	2		2 - Coverage - Pervious	0.19
14	1		1 - Coverage - Pervious	0.26
15	1		1 - Coverage - Pervious	0.06
16	1		1 - Coverage - Pervious	0.51
IMPORT_PARCELS

Cell Formulas
Range	Formula
F2:F16	F2	=IF(B2="","",D2&" "&"-"&" "&B2)
G2:G16	G2	=IF(C2="","",(C2/43560))

Parcel Storm Drainage Import Tool.xlsm
	J	K	L	M	N	O	P	Q	R
1
2	- Coverage - Impervious	0.16				ID	Impervious	Pervious	Roof
3	1 - Coverage - Impervious	0.22				-	0.16
4	1 - Coverage - Pervious	0.89				1	0.22	0.89
5	2 - Coverage - Impervious	0.08				2	0.08	0.75
6	2 - Coverage - Pervious	0.75				3	0.02	0.43	0.17
7	3 - Coverage - Impervious	0.02				4	0.37	0.4	0.22
8	3 - Coverage - Pervious	0.43
9	3 - Coverage - Roof	0.17
10	4 - Coverage - Impervious	0.37
11	4 - Coverage - Pervious	0.40
12	4 - Coverage - Roof	0.22
13
14
15
16
IMPORT_PARCELS

 

[rcb007]

I have made some progress. My question now is how can I create a lookup within P3 to the consolidation table?

I hope this makes more sense. Thank you!

Parcel Storm Drainage Import Tool.xlsm
	I	J	K	L	M	N	O	P	Q	R	S
1	Dynamic	Dynamic
2			0.00				ID	Impervious	Pervious	Roof
3	0	0 - Coverage - Impervious	0.16				0	0.16
4	1	1 - Coverage - Impervious	0.22				1	0.22	0.89
5	2	1 - Coverage - Pervious	0.89				2	0.08	0.75
6	3	2 - Coverage - Impervious	0.08				3	0.02	0.43	0.17
7	4	2 - Coverage - Pervious	0.75				4	0.37	0.4	0.22
8		3 - Coverage - Impervious	0.02
9		3 - Coverage - Pervious	0.43
10		3 - Coverage - Roof	0.17
11		4 - Coverage - Impervious	0.37				P3 = Lookup with P2 and O3 within Columns J and Value from K
12		4 - Coverage - Pervious	0.40				Q3 = Lookup with Q2 and O3 within Columns J and Value from K
13		4 - Coverage - Roof	0.22				R3 = Lookup with R2 and O3 within Columns J and Value from K
14
15							* If a value is not shown within Column K, display a Blank Cell.
16
17
18
IMPORT_PARCELS

Cell Formulas
Range	Formula
J2:K13	J2	=SORT(CHOOSE({1,2},UNIQUE(F2:F1000),SUMIFS(G2:G1000,F2:F1000,UNIQUE(F2:F1000))))
I3:I7	I3	=SORT(UNIQUE(E2:E1000))
Dynamic array formulas.