In the below data, we need to select columns with the same "Type" (i.e Type1, Type2 ) and sum them. There could be any number of columns under each header "Type", also one "Type" will be in adjacent rows only.
The output should have sum of all columns where there is "Type1" in row2
Input:
| F1 | F2 | F3 | F4 | F5 | F6 | F7 | F8 | F9 | F10 | F11 |
| | Type1 | Type1 | Type1 | Type1 | Type1 | Type1 | Type2 | Type2 | Type2 | Type2 |
| #### | Sec_1 | Sec_2 | Sec_3 | Sec_4 | Sec_5 | Sec_N | Sec_8 | Sec_2 | Sec_3 | Sec_20 |
| Account_1 | $3.00 | $3.00 | $4.00 | $3.00 | $4.00 | $1.00 | $4.00 | $0.00 | $4.00 | $2.00 |
| Account_2 | $2.00 | $1.00 | $3.00 | $4.00 | $0.00 | $2.00 | $4.00 | $0.00 | $0.00 | $5.00 |
| Account_3 | $3.00 | $0.00 | $3.00 | $2.00 | $4.00 | $0.00 | $1.00 | $5.00 | $0.00 | $5.00 |
| Account_4 | $1.00 | $5.00 | $5.00 | $0.00 | $0.00 | $4.00 | $4.00 | $3.00 | $1.00 | $5.00 |
| Account_5 | $5.00 | $2.00 | $2.00 | $2.00 | $1.00 | $0.00 | $2.00 | $5.00 | $3.00 | $3.00 |
| Account_N | $0.00 | $5.00 | $1.00 | $2.00 | $2.00 | $3.00 | $0.00 | $5.00 | $4.00 | $2.00 |
Expected Output:
| F1 | F2 | F3 | F4 | F5 | F6 | F7 | F8 | F9 | F10 | F11 | F12 | F13 |
| | Type1 | Type1 | Type1 | Type1 | Type1 | Type1 | | Type2 | Type2 | Type2 | Type2 | |
| #### | Sec_1 | Sec_2 | Sec_3 | Sec_4 | Sec_5 | Sec_N | Sum_Type1 | Sec_8 | Sec_2 | Sec_3 | Sec_20 | Sum_Type2 |
| Account_1 | $3.00 | $3.00 | $4.00 | $3.00 | $4.00 | $1.00 | $18.00 | $4.00 | $0.00 | $4.00 | $2.00 | $10.00 |
| Account_2 | $2.00 | $1.00 | $3.00 | $4.00 | $0.00 | $2.00 | $12.00 | $4.00 | $0.00 | $0.00 | $5.00 | $9.00 |
| Account_3 | $3.00 | $0.00 | $3.00 | $2.00 | $4.00 | $0.00 | $12.00 | $1.00 | $5.00 | $0.00 | $5.00 | $11.00 |
| Account_4 | $1.00 | $5.00 | $5.00 | $0.00 | $0.00 | $4.00 | $15.00 | $4.00 | $3.00 | $1.00 | $5.00 | $13.00 |
| Account_5 | $5.00 | $2.00 | $2.00 | $2.00 | $1.00 | $0.00 | $12.00 | $2.00 | $5.00 | $3.00 | $3.00 | $13.00 |
| Account_N | $0.00 | $5.00 | $1.00 | $2.00 | $2.00 | $3.00 | $13.00 | $0.00 | $5.00 | $4.00 | $2.00 | $11.00 |
please assist.
