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

Monty85

Board Regular
Joined
May 6, 2019
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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,

IDNameFee
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​
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
ABCDEFGHIJ
1IDNameFeeEffective dateIDNameEffective dateSum of Fee
21A Brown-4.0830/04/20191A Brown30/04/2019-4.08
31A Brown-4.0631/05/20191A Brown31/05/2019-4.06
41A Brown-33.0930/06/20191A Brown30/06/2019-7.48
51A Brown-7.4830/06/20192A Brown31/07/2019-7.41
61A Brown33.0930/06/20192B White31/08/2019-7.37
72A Brown-7.4131/07/20192B White30/11/2019-21.87
82B White-7.3731/08/20193C Green31/05/2020-7.06
92B White-7.3330/11/20193C Green30/06/2020-7.06
102B White-7.2930/11/20193C Green31/07/2020-7.00
112B White-7.2530/11/20193C Green31/08/2020-6.99
123C Green-7.0631/05/20204D Black31/12/2020-6.96
133C Green-86.4730/06/20204D Black31/01/2021-6.95
143C Green-7.0630/06/20204D Black30/06/2021-14.39
153C Green86.4730/06/20205E Red11/06/20210.00
163C Green-7.0031/07/20205E Red31/10/2021-7.52
173C Green-6.9931/08/20205E Red19/11/2021-4.81
184D Black-6.9631/12/2020
194D Black-6.9531/01/2021
204D Black-6.9230/06/2021
214D Black-83.9930/06/2021
224D Black-7.4730/06/2021
234D Black83.9930/06/2021
245E Red-7.5231/10/2021
255E Red-30.2511/06/2021
265E Red30.2511/06/2021
275E Red-4.8119/11/2021
Sheet1
 
Upvote 0
Would a Pivot Table like this be suitable?

Monty85.xlsm
ABCDEFGHIJ
1IDNameFeeEffective dateIDNameEffective dateSum of Fee
21A Brown-4.0830/04/20191A Brown30/04/2019-4.08
31A Brown-4.0631/05/20191A Brown31/05/2019-4.06
41A Brown-33.0930/06/20191A Brown30/06/2019-7.48
51A Brown-7.4830/06/20192A Brown31/07/2019-7.41
61A Brown33.0930/06/20192B White31/08/2019-7.37
72A Brown-7.4131/07/20192B White30/11/2019-21.87
82B White-7.3731/08/20193C Green31/05/2020-7.06
92B White-7.3330/11/20193C Green30/06/2020-7.06
102B White-7.2930/11/20193C Green31/07/2020-7.00
112B White-7.2530/11/20193C Green31/08/2020-6.99
123C Green-7.0631/05/20204D Black31/12/2020-6.96
133C Green-86.4730/06/20204D Black31/01/2021-6.95
143C Green-7.0630/06/20204D Black30/06/2021-14.39
153C Green86.4730/06/20205E Red11/06/20210.00
163C Green-7.0031/07/20205E Red31/10/2021-7.52
173C Green-6.9931/08/20205E Red19/11/2021-4.81
184D Black-6.9631/12/2020
194D Black-6.9531/01/2021
204D Black-6.9230/06/2021
214D Black-83.9930/06/2021
224D Black-7.4730/06/2021
234D Black83.9930/06/2021
245E Red-7.5231/10/2021
255E Red-30.2511/06/2021
265E Red30.2511/06/2021
275E Red-4.8119/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.
 
Upvote 0
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
 
Upvote 1
  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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top