Macro to move column data to new rows under groupings

[HeatherF]

Hello,

I have an excel spreadsheet listing Clients (columns B/C) and the people they're connected to, with 1 row for every connection. So, since Adam (rows 2-4) has 3 relatives in the database, he has 3 rows.

I would like to create a macro that groups rows by Client, so you click on Adam, and rows with his relatives appear beneath him, ideally summing the Jnt LTG columns of himself and his relatives. I tried making a pivot table, but showing all the data associated with each relative created a very long nested outline in column 1.

Is there any way to have the data associated with each relative appear as columns in a single row under the Client header? I've included my existing spreadsheet below. Also, a very manual mockup of what I would like to achieve, based on the first two Clients in the spreadsheet.

Thank you so much!
Heather

Existing Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID#	Affiliation	Pref Name w Suffix	Rating	5-yr Giving	Jnt LTG	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving	Relation Jnt LTG
2	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	6529599	Parent	Dulcinea Petschelt	9	$ 100,215.00	$ 285,874.13
3	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10073058	Inlaw	Dana Pell	8	$ -	$ 468,390.00
4	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10029576	Parent	Hermy Calabry	9	$ -	$ 285,874.13
5	10025811	FYE/AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	6406626	Parent	Larissa Alejandre	9	$ 3,841.79	$ 63,486.54
6	10025811	FYE/AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030236	Sibling	Christiana Cubin		$ 650.00	$ 1,305.00
7	10025811	FYE/AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030996	Parent	Simeon Bovaird	9	$ -	$ 63,486.54
8	10025841	FYE/AGENTS	Neely Beisley		$ 2,000.00	$ 9,579.30	6219113	Uncle	Joete Kloss	7	$ 8,650.00	$ 1,318,286.25
9	10025859	FYE/AGENTS	Wenonah Mort	CW	$ 500.00	$ 4,069.97	5886150	Parent	Wilow Davisson	8	$ 138,047.03	$ 793,357.85
10	10025906	FYE	Valentijn Peddowe	CW	$ -	$ 170.82	10076736	Niece	Frederique Randalson	CW	$ -	$ -
11	10025906	FYE	Valentijn Peddowe	CW	$ -	$ 170.82	10076742	Inlaw	Arluene Grocutt	6	$ 7,500.00	$ 1,047,020.00
12	10025918	FYE	Graig Flipsen	CW	$ 75.90	$ 21,275.60	4123150	Grandparent	Felicio Buttress	9	$ -	$ 4,356,175.13
13	10025918	FYE	Graig Flipsen	CW	$ 75.90	$ 21,275.60	10014561	Grandparent	Talya Alford	6	$ -	$ 4,356,175.13
14	10025918	FYE	Graig Flipsen	CW	$ 75.90	$ 21,275.60	10019897	Sibling	Alaine Hurton	CW	$ 49,500.00	$ 97,775.00
15	10026014	AGENTS	Loleta Ducarne	9	$ 2,000.00	$ 2,955.00
16	10026025	FYE	Fredrika Gettens	9	$ 1,600.00	$ 2,570.00	10019980	Sibling	Theresa Zylbermann	U	$ 162.50	$ 597.50
17	10026025	FYE	Fredrika Gettens	9	$ 1,600.00	$ 2,570.00	6798524	Parent	Theresa Zylbermann	U	$ -	$ 1,065.00
18	10026045	FYE	Kati Wrought		$ 4,917.50	$ 27,224.97	10025813	Spouse/Partner	Sallyann Blei	9	$ 4,917.50	$ 27,224.97
19	10026086	FYE	Hammad Kikke	9	$ 20,180.31	$ 71,055.55	10041644	Sibling	Ogdon Karet	U	$ 175.00	$ 985.04
20	10026086	FYE	Hammad Kikke	9	$ 20,180.31	$ 71,055.55	10030584	Inlaw	Flossy Mclie		$ -	$ 55.00
21	10026086	FYE	Hammad Kikke	9	$ 20,180.31	$ 71,055.55	10032632	Sibling	Nev Philippart	10	$ 150.00	$ 205.00
22	10026121	FYE	Dill Korneichuk	7	$ 57,500.00	$ 96,830.00
23	10026178	FYE	Ingemar Barrows	7	$ 45,131.97	$ 80,197.79	10079908	Parent	Pippo Duffell		$ 465.12	$ 3,915.45
24	10026178	FYE	Ingemar Barrows	7	$ 45,131.97	$ 80,197.79	10059330	Sibling	Haleigh Godain	U	$ 40.00	$ 160.00
25	10028268	CREW	Heddie Hawyes	CW	$ 850.00	$ 3,145.00	3421331	Grandparent	Tessi Goodluck	U	$ -	$ 9,844.37
26	10028268	CREW	Heddie Hawyes	CW	$ 850.00	$ 3,145.00	10015662	Sibling	Audie Easbie	CW	$ 100.00	$ 275.00
27	10028268	CREW	Heddie Hawyes	CW	$ 850.00	$ 3,145.00	6221340	Parent	Becky Cattle	6	$ 4,366.41	$ 308,554.06
28	10030321	CREW	Angie Lelievre	8	$ 216,400.00	$ 217,585.00	10030933	Parent	Johnathon Edge	U	$ -	$ 50.00
29	10030321	CREW	Angie Lelievre	8	$ 216,400.00	$ 217,585.00	10035814	Sibling	Klarika Turnpenny	10	$ 180.00	$ 13,505.00
30	10030321	CREW	Angie Lelievre	8	$ 216,400.00	$ 217,585.00	10057207	Sibling	Marla Dyball	N	$ 190.00	$ 241.00
Sheet3

Desired Result

Book1
	A	B	C	D	E	F	G
1	ID#	Affiliation	Pref Name w Suffix	Rating	5-yr Giving	Jnt LTG	Sum of Associated LTG
2	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	$ 1,064,625.20
3	6529599	Parent	Dulcinea Petschelt	9	$ 100,215.00	$ 285,874.13
4	10073058	Inlaw	Dana Pell	8	$ -	$ 468,390.00
5	10029576	Parent	Hermy Calabry	9	$ -	$ 285,874.13
6	10025811	FYE/AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	$ 135,293.08
7	6406626	Parent	Larissa Alejandre	9	$ 3,841.79	$ 63,486.54
8	10030236	Sibling	Christiana Cubin		$ 650.00	$ 1,305.00
9	10030996	Parent	Simeon Bovaird	9	$ -	$ 63,486.54
Sheet1

Cell Formulas
Range	Formula
G2,G6	G2	=SUM(F2:F5)

 

[Jeffrey Mahoney]

HeatherF

A couple things to start. You don't necessarily need to sort or filter the data to get the sum of each client. You can SUMIFS. =SUMIFS(L:L,C:C,C2)

The UNIQUE function could be used in another table to pull all the client names and then SUMIFS to summarize the values

 

[Jeffrey Mahoney]

But then again, a pivot table is just as good

 

[HeatherF]

HeatherF

A couple things to start. You don't necessarily need to sort or filter the data to get the sum of each client. You can SUMIFS. =SUMIFS(L:L,C:C,C2)

The UNIQUE function could be used in another table to pull all the client names and then SUMIFS to summarize the values

Thank you--that is interesting. It may be the quickest way to go about it. In the example below, I've used your code, and added the Jnt LTG of the Client. But is there a way to roll up rows with identical ID#s under headers so you can expand and collapse each Client?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID#	Affiliation	Pref Name w Suffix	Rating	5-yr Giving	Jnt LTG	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving	Relation Jnt LTG	TOTAL Associated LTG
2	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	6529599	Parent	Dulcinea Petschelt	9	$ 100,215.00	$ 285,874.13	$ 1,064,625.20
3	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10073058	Inlaw	Dana Pell	8	$ -	$ 468,390.00	$ 1,064,625.20
4	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10029576	Parent	Hermy Calabry	9	$ -	$ 285,874.13	$ 1,064,625.20
5	10025811	FYE	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	6406626	Parent	Larissa Alejandre	9	$ 3,841.79	$ 63,486.54	$ 135,293.08
6	10025811	FYE	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030236	Sibling	Christiana Cooch		$ 650.00	$ 1,305.00	$ 135,293.08
7	10025811	FYE	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030996	Parent	Simeon Bovaird	9	$ -	$ 63,486.54	$ 135,293.08
Sheet4

Cell Formulas
Range	Formula
M2:M7	M2	=SUMIFS(L:L,C:C,C2)+F2

 

[HeatherF]

I thought I'd found an answer. I used the subtotal dialog to COUNT at every change in the Pref Name field and add the subtotal to column N, but I don't know why the count is O. Shouldn't it be 3 for the first person?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID#	Affiliation	Pref Name w Suffix	Rating	5-yr Giving	Jnt LTG	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving	Relation Jnt LTG	TOTAL Associated LTG	TOTAL Associated Prospects
2			Grand Count											0
3			Adam Kielt Count											0
4	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	6529599	Parent	Dulcinea Petschelt	9	$ 100,215.00	$ 285,874.13	$ 1,064,625.20
5	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10073058	Inlaw	Dana Pell	8	$ -	$ 468,390.00	$ 1,064,625.20
6	10025692	AGENTS	Adam Kielt	9	$ 2,750.00	$ 24,486.94	10029576	Parent	Hermy Calabry	9	$ -	$ 285,874.13	$ 1,064,625.20
7			Felike Pilbury Count											0
8	10025811	FYE	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	6406626	Parent	Larissa Alejandre	9	$ 3,841.79	$ 63,486.54	$ 200,084.62
9	10025811	AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	6406626	Parent	Larissa Alejandre	9	$ 3,841.79	$ 63,486.54	$ 200,084.62
10	10025811	FYE	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030236	Sibling	Christiana Cooch		$ 650.00	$ 1,305.00	$ 200,084.62
11	10025811	AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030236	Sibling	Christiana Cooch		$ 650.00	$ 1,305.00	$ 200,084.62
12	10025811	AGENTS	Felike Pilbury	9	$ 4,475.00	$ 7,015.00	10030996	Parent	Simeon Bovaird	9	$ -	$ 63,486.54	$ 200,084.62
13			Neely Beisley Count											0
14	10025841	AGENTS	Neely Beisley		$ 2,000.00	$ 9,579.30	6219113	Uncle	Joete Kloss	7	$ 8,650.00	$ 1,318,286.25	$ 1,327,865.55
Sheet2

Cell Formulas
Range	Formula
N2	N2	=SUBTOTAL(3,N4:N14)
N3	N3	=SUBTOTAL(3,N4:N6)
M14,M8:M12,M4:M6	M4	=SUMIFS(L:L,C:C,C4)+F4
N7	N7	=SUBTOTAL(3,N8:N12)
N13	N13	=SUBTOTAL(3,N14:N14)

 

[Jeffrey Mahoney]

You would probably want to use Countifs using the agent's name column to count and the relative's name to not equal the agent's name.

Concerning rolling up the list without using a pivot table... A macro could do that. It would have to sort, then go and group all the agents together. The formula in the Jnt total column could be restricted to only show if the Agent's name doesn't equal the relative name. A macro could be created in a couple hours to get it right.