Forum Discussion
Combine rows based on Unique ID
I have a table that I would like to combine based on the ID.
| ID | Jan | Feb | Mar | Apr | May | Jun | Jul | Ago | Sep |
| 1 | 9 | 3 | |||||||
| 1 | 7 | 1 | 2 | ||||||
| 1 | 6 | 7 | 7 | ||||||
| 2 | 1 | 4 | |||||||
| 2 | 4 | 3 | |||||||
| 2 | 8 | 7 | |||||||
| 3 | 5 | 2 | 10 | ||||||
| 3 | 1 | 6 | 6 | ||||||
| 3 | 10 | 6 | 2 | ||||||
| 4 | 8 | 10 | |||||||
| 4 | 1 | 5 | 8 | ||||||
| 4 | 3 | 6 |
To have the following outcome:
(And calculate the average in the row counting blanks)
| ID | Jan | Feb | Mar | Apr | May | Jun | Jul | Ago | Sep | Average |
| 1 | 9 | 3 | 7 | 1 | 2 | 6 | 7 | 7 | 4.666667 | |
| 2 | 1 | 4 | 4 | 3 | 8 | 7 | 3 | |||
| 3 | 5 | 2 | 10 | 1 | 6 | 6 | 10 | 6 | 2 | 5.333333 |
| 4 | 8 | 10 | 1 | 5 | 8 | 3 | 6 | 4.555556 |
Thanks,
1 Reply
Anonymous , If you take the sum of all these columns, it will combine. Also, you can unpivot the data(Month) and then use in matrix, with sum of values column you will get
