Ok so some quick random sample data below for the scenario.
So basically I need to do the following per Account(column A)
Each account will have a Department- column C. I need to create a single row for each Department within each Account that combines the values on Columns F-K, the dates. They are divided into two rows based on the option fields for whether they go into one or another, but I basically need them to just skip the blanks, ignore the options and just put all of the values per department into a single row. I put an after version of what I'm hoping for below the original sample as well to illustrate it better than I can explain. I feel like this is very simple, but I'm not sure how to approach it.
Any help is amazing! Thank you!
Original:
<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>
End Result:
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
So basically I need to do the following per Account(column A)
Each account will have a Department- column C. I need to create a single row for each Department within each Account that combines the values on Columns F-K, the dates. They are divided into two rows based on the option fields for whether they go into one or another, but I basically need them to just skip the blanks, ignore the options and just put all of the values per department into a single row. I put an after version of what I'm hoping for below the original sample as well to illustrate it better than I can explain. I feel like this is very simple, but I'm not sure how to approach it.
Any help is amazing! Thank you!
Original:
Account Name | Account code | Department | Option A | Option B | January | February | March | April | May | June |
Account 1 | 123456 | 1 | A | 10 | 24 | 17 | 19 | 34 | 13 | |
Account 1 | 123456 | 2 | A | 21 | 45 | 22 | 13 | |||
Account 1 | 123456 | 2 | B | 35 | 44 | |||||
Account 2 | 234567 | 1 | A | 42 | 49 | |||||
Account 2 | 234567 | 1 | B | 33 | 25 | 43 | 22 | |||
Account 2 | 234567 | 2 | A | 26 | 48 | 44 | 12 | |||
Account 2 | 234567 | 2 | B | 26 | 24 | |||||
Account 3 | 345678 | 1 | A | 36 | 39 | |||||
Account 3 | 345678 | 1 | B | 35 | 36 | 18 | 19 | |||
Account 3 | 345678 | 2 | A | 14 | 26 | 10 | ||||
Account 3 | 345678 | 2 | B | 44 | 25 | 11 |
<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>
End Result:
Account Name | Account code | Department | Option A | Option B | January | February | March | April | May | June |
Account 1 | 123456 | 1 | 19 | 18 | 23 | 42 | 12 | 28 | ||
Account 1 | 123456 | 2 | 35 | 11 | 24 | 49 | 23 | 18 | ||
Account 2 | 234567 | 1 | 39 | 34 | 32 | 30 | 33 | 25 | ||
Account 2 | 234567 | 2 | 32 | 25 | 23 | 34 | 34 | 47 | ||
Account 3 | 345678 | 1 | 28 | 36 | 45 | 38 | 49 | 18 | ||
Account 3 | 345678 | 2 | 11 | 49 | 49 | 26 | 26 | 42 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>