Excel Brain Fog

[jmd78]

Help, I can not for the life of me figure out how to sift through the data in this small spreadsheet to ascertain and build a table
that shows the various unit sizes by unit types. I can use delete duplicates and get to the unique units, then countif to get the number of the units for each size and sum if to get the square footage by unit size, but this is over all of the unit types (both CC and NCC). My dilemma is how to do this and get the information seperated by the unit type. SEE FILE AT SHAREPOINT LINK: Unit Matrix.xlsx
Your help and sugestions are most appreciated.

 

[alansidman]

With Power Query. three queries

All

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}})
in
    #"Grouped Rows"

CC

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE", "TYPE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([TYPE] = "CC"))
in
    #"Filtered Rows"

NCC

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE", "TYPE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [TYPE] = "NCC")
in
    #"Filtered Rows"

Unit Matrix.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T
19	UNIT SIZE	Count	Square Feet		UNIT SIZE	TYPE	Count	Square Feet		UNIT SIZE	TYPE	Count	Square Feet
20	10x10	53	5300		10x10	CC	42	4200		10x20	NCC	6	1200
21	10x20	19	3800		10x20	CC	13	2600		8x20	NCC	1	160
22	10x15	18	2700		10x15	CC	18	2700		10x10	NCC	11	1100
23	8x15	2	240		8x15	CC	2	240
24	8x10	3	240		8x10	CC	3	240
25	10x25	6	1500		10x25	CC	6	1500
26	5x5	8	200		5x5	CC	8	200
27	15x10	3	450		15x10	CC	3	450
28	10x5	6	300		10x5	CC	6	300
29	5x15	3	225		5x15	CC	3	225
30	8x20	1	160
Rent Roll AsOf

 

[jmd78]

With Power Query. three queries

All

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}})
in
    #"Grouped Rows"

CC

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE", "TYPE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([TYPE] = "CC"))
in
    #"Filtered Rows"

NCC

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UNIT#(lf)SIZE", "TYPE"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Square Feet", each List.Sum([#"SF#(lf)UNIT"]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [TYPE] = "NCC")
in
    #"Filtered Rows"

Unit Matrix.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T
19	UNIT SIZE	Count	Square Feet		UNIT SIZE	TYPE	Count	Square Feet		UNIT SIZE	TYPE	Count	Square Feet
20	10x10	53	5300		10x10	CC	42	4200		10x20	NCC	6	1200
21	10x20	19	3800		10x20	CC	13	2600		8x20	NCC	1	160
22	10x15	18	2700		10x15	CC	18	2700		10x10	NCC	11	1100
23	8x15	2	240		8x15	CC	2	240
24	8x10	3	240		8x10	CC	3	240
25	10x25	6	1500		10x25	CC	6	1500
26	5x5	8	200		5x5	CC	8	200
27	15x10	3	450		15x10	CC	3	450
28	10x5	6	300		10x5	CC	6	300
29	5x15	3	225		5x15	CC	3	225
30	8x20	1	160
Rent Roll AsOf

 

[jmd78]

Love the idea of using this, but I have never worked with it. No earthly idea how to apply even after watching several videos on Power Query. Ended up just doing it the old way, deleting duplicates, sorting to build a table for the index, and running if(and statements and countifs statements.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.