How to avoid making multiple tables to achieve this summary view?

[dejavu619]

Data looks like this:

https://imgur.com/gg0YorZ

I need to prepare summaries like this:

https://imgur.com/q7NqGst

https://imgur.com/4T7lVFs

What I currently do is first prepare a pivot table showing a level higher (Customer level) and then add a helper column flagging customers as either Growning or Degrowning. I do this for Families as well (so that they can be flagged as either Growing or Degrowing) and then take a pivot to get the summary I like.

I don't mind doing this without pivot tables if it's better. I just want to avoid making intermediate summaries at a Customer / Family level (Data is at an account level).

 

[Herakles]

Data looks like this:

https://imgur.com/gg0YorZ

I need to prepare summaries like this:

https://imgur.com/q7NqGst

https://imgur.com/4T7lVFs

What I currently do is first prepare a pivot table showing a level higher (Customer level) and then add a helper column flagging customers as either Growning or Degrowning. I do this for Families as well (so that they can be flagged as either Growing or Degrowing) and then take a pivot to get the summary I like.

I don't mind doing this without pivot tables if it's better. I just want to avoid making intermediate summaries at a Customer / Family level (Data is at an account level).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[dejavu619]

Book2
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3	Data							Summary
4	Region	Account Number	Customer ID	Family ID	Change ($)			Region	# Distinct Custs	# Distinct Fams	Growth / Degrowth ($)	# Distinct Growing Fams	Growth ($)	# Distinct Degrowing Fams	Degrowth ($)
5	North 1	123456789	1331	F001	-5
6	North 1	123456790	1331	F001	-10
7	North 1	123456791	1331	F001	99
8	North 1	123456792	1332	1332	1389
9	North 1	123456793	1334	1334	31
10	North 1	123456794	1337	1337	-2380
11	North 1	123456795	1340	1340	28047
12	North 1	123456796	1343	1343	-9382
13	North 1	123456797	1346	F002	2874
14	North 1	123456798	1349	F002	298472
15	North 2	123456799	1352	F002	-5222
16	North 2	123456800	1355	F003	254
17	North 2	123456801	1355	F003	8888
18	South 1	123456802	1355	F003	-942
19	South 2	123456803	1355	F003	15568
Sheet1

 

[Herakles]

Data looks like this:

https://imgur.com/gg0YorZ

I need to prepare summaries like this:

https://imgur.com/q7NqGst

https://imgur.com/4T7lVFs

What I currently do is first prepare a pivot table showing a level higher (Customer level) and then add a helper column flagging customers as either Growning or Degrowning. I do this for Families as well (so that they can be flagged as either Growing or Degrowing) and then take a pivot to get the summary I like.

I don't mind doing this without pivot tables if it's better. I just want to avoid making intermediate summaries at a Customer / Family level (Data is at an account level).

[

Can you please explain how you determine whether to mark a customer (I assume that these are represented by Account Number) and families as Growing or Degrowing?

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to avoid making multiple tables to achieve this summary view?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.