Best solution to consolidate values that occur on multiple dates/lines

[Monty85]

Im trying to find the best solution to aggregate fee data from a data set where there are multiple fees that are recorded on the same day.

The outcome i'm trying to acheive is to have the data in the same format as below but with the values that occur on multiple data consolidated to one line/value.

I tried playing with Pivot Tables but I couldn't work out how to keep the format the same. Hoping someone here might have a suggestion I can try.

Many thanks,

ID	Name	Fee	Effective date
1​	A Brown	-4.08​	30/04/2019​
1​	A Brown	-4.06​	31/05/2019​
1​	A Brown	-33.09​	30/06/2019​
1​	A Brown	-7.48​	30/06/2019​
1​	A Brown	33.09​	30/06/2019​
2​	A Brown	-7.41​	31/07/2019​
2​	B White	-7.37​	31/08/2019​
2​	B White	-7.33​	30/11/2019​
2​	B White	-7.29​	30/11/2019​
2​	B White	-7.25​	30/11/2019​
3​	C Green	-7.06​	31/05/2020​
3​	C Green	-86.47​	30/06/2020​
3​	C Green	-7.06​	30/06/2020​
3​	C Green	86.47​	30/06/2020​
3​	C Green	-7​	31/07/2020​
3​	C Green	-6.99​	31/08/2020​
4​	D Black	-6.96​	31/12/2020​
4​	D Black	-6.95​	31/01/2021​
4​	D Black	-6.92​	30/06/2021​
4​	D Black	-83.99​	30/06/2021​
4​	D Black	-7.47​	30/06/2021​
4​	D Black	83.99​	30/06/2021​
5​	E Red	-7.52​	31/10/2021​
5​	E Red	-30.25​	6/11/2021​
5​	E Red	30.25​	6/11/2021​
5​	E Red	-4.81​	19/11/2021​

 

[Peter_SSs]

I tried playing with Pivot Tables but I couldn't work out how to keep the format the same.

Would a Pivot Table like this be suitable?

Monty85.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Name	Fee	Effective date			ID	Name	Effective date	Sum of Fee
2	1	A Brown	-4.08	30/04/2019			1	A Brown	30/04/2019	-4.08
3	1	A Brown	-4.06	31/05/2019			1	A Brown	31/05/2019	-4.06
4	1	A Brown	-33.09	30/06/2019			1	A Brown	30/06/2019	-7.48
5	1	A Brown	-7.48	30/06/2019			2	A Brown	31/07/2019	-7.41
6	1	A Brown	33.09	30/06/2019			2	B White	31/08/2019	-7.37
7	2	A Brown	-7.41	31/07/2019			2	B White	30/11/2019	-21.87
8	2	B White	-7.37	31/08/2019			3	C Green	31/05/2020	-7.06
9	2	B White	-7.33	30/11/2019			3	C Green	30/06/2020	-7.06
10	2	B White	-7.29	30/11/2019			3	C Green	31/07/2020	-7.00
11	2	B White	-7.25	30/11/2019			3	C Green	31/08/2020	-6.99
12	3	C Green	-7.06	31/05/2020			4	D Black	31/12/2020	-6.96
13	3	C Green	-86.47	30/06/2020			4	D Black	31/01/2021	-6.95
14	3	C Green	-7.06	30/06/2020			4	D Black	30/06/2021	-14.39
15	3	C Green	86.47	30/06/2020			5	E Red	11/06/2021	0.00
16	3	C Green	-7.00	31/07/2020			5	E Red	31/10/2021	-7.52
17	3	C Green	-6.99	31/08/2020			5	E Red	19/11/2021	-4.81
18	4	D Black	-6.96	31/12/2020
19	4	D Black	-6.95	31/01/2021
20	4	D Black	-6.92	30/06/2021
21	4	D Black	-83.99	30/06/2021
22	4	D Black	-7.47	30/06/2021
23	4	D Black	83.99	30/06/2021
24	5	E Red	-7.52	31/10/2021
25	5	E Red	-30.25	11/06/2021
26	5	E Red	30.25	11/06/2021
27	5	E Red	-4.81	19/11/2021
Sheet1

 

[Monty85]

Would a Pivot Table like this be suitable?

Monty85.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Name	Fee	Effective date			ID	Name	Effective date	Sum of Fee
2	1	A Brown	-4.08	30/04/2019			1	A Brown	30/04/2019	-4.08
3	1	A Brown	-4.06	31/05/2019			1	A Brown	31/05/2019	-4.06
4	1	A Brown	-33.09	30/06/2019			1	A Brown	30/06/2019	-7.48
5	1	A Brown	-7.48	30/06/2019			2	A Brown	31/07/2019	-7.41
6	1	A Brown	33.09	30/06/2019			2	B White	31/08/2019	-7.37
7	2	A Brown	-7.41	31/07/2019			2	B White	30/11/2019	-21.87
8	2	B White	-7.37	31/08/2019			3	C Green	31/05/2020	-7.06
9	2	B White	-7.33	30/11/2019			3	C Green	30/06/2020	-7.06
10	2	B White	-7.29	30/11/2019			3	C Green	31/07/2020	-7.00
11	2	B White	-7.25	30/11/2019			3	C Green	31/08/2020	-6.99
12	3	C Green	-7.06	31/05/2020			4	D Black	31/12/2020	-6.96
13	3	C Green	-86.47	30/06/2020			4	D Black	31/01/2021	-6.95
14	3	C Green	-7.06	30/06/2020			4	D Black	30/06/2021	-14.39
15	3	C Green	86.47	30/06/2020			5	E Red	11/06/2021	0.00
16	3	C Green	-7.00	31/07/2020			5	E Red	31/10/2021	-7.52
17	3	C Green	-6.99	31/08/2020			5	E Red	19/11/2021	-4.81
18	4	D Black	-6.96	31/12/2020
19	4	D Black	-6.95	31/01/2021
20	4	D Black	-6.92	30/06/2021
21	4	D Black	-83.99	30/06/2021
22	4	D Black	-7.47	30/06/2021
23	4	D Black	83.99	30/06/2021
24	5	E Red	-7.52	31/10/2021
25	5	E Red	-30.25	11/06/2021
26	5	E Red	30.25	11/06/2021
27	5	E Red	-4.81	19/11/2021
Sheet1

Yes that looks right.

Can I please ask how you set up the Pivot Table to end up with that format? I did attempt to mess around with a few options but it always ended up unusable.

 

[Peter_SSs]

how you set up the Pivot Table to end up with that format?

1. Set up a basic PT with ID, Name and Effective Date in the Rows section and (Sum of) Fee in the Values section
2. Click anywhere in the new PT and go to the Design tab on the ribbon
3. Subtotals drop-down -> Do Not Show Subtotals
4. Grand Totals drop-down -> Off for Rows and Columns
5. Report Layout drop-down -> Show in Tabular Form
6. Report Layout drop-down -> Repeat All Item Labels

 

[Monty85]

1. Set up a basic PT with ID, Name and Effective Date in the Rows section and (Sum of) Fee in the Values section
2. Click anywhere in the new PT and go to the Design tab on the ribbon
3. Subtotals drop-down -> Do Not Show Subtotals
4. Grand Totals drop-down -> Off for Rows and Columns
5. Report Layout drop-down -> Show in Tabular Form
6. Report Layout drop-down -> Repeat All Item Labels

That did it. You are truly the master

Thanks you so much for the help - can't imagine I would have worked that out on my own.

 

[Peter_SSs]

Thanks you so much for the help

You're welcome. Thanks for the follow-up.

can't imagine I would have worked that out on my own.

Don't worry, I had to have it explained to me originally too.