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.
Solucionado! Ir para Solução.
Thanks a lot, it worked!!!!
I used Find and Replace tool instead of the last Union keeping F1 as key.
@Kshitij14 , did the column rename/sorting for you. Not very pretty but should work!