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,
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 |