Hi Everyone,
I am not sure what is the best way of solving this problem whether its some advanced function, or VBA/Macro route.
What I am trying to accomplish is to merge and sum multiple rows for the same invoice number. So, if for INV1 there's multiple product line with same product line name (i.e. TIre) then should Sum Column E for those product lines and delete rest of duplicate rows. Then check rest of product lines under Product Line for INV1 and see there's no multiple product line (ie Camera) then keep as is. then move on to the next invoice
<colgroup><col width="158" span="5" style="width: 119pt;"></colgroup><tbody>
</tbody>
Where the final sheet should look something like this after all analysis:
<colgroup><col width="158" span="5" style="width: 119pt;"></colgroup><tbody>
</tbody>
Thank you
I am not sure what is the best way of solving this problem whether its some advanced function, or VBA/Macro route.
What I am trying to accomplish is to merge and sum multiple rows for the same invoice number. So, if for INV1 there's multiple product line with same product line name (i.e. TIre) then should Sum Column E for those product lines and delete rest of duplicate rows. Then check rest of product lines under Product Line for INV1 and see there's no multiple product line (ie Camera) then keep as is. then move on to the next invoice
Column A Invoice | Column B Product Line | Column C Random | Column D Date | Column E Amount |
INV1 | Tire | 1 | 03/01/15 | $226.10 |
INV1 | Tire | 1 | 03/01/15 | $170.00 |
INV1 | Tire | 1 | 03/01/15 | $676.83 |
INV1 | Camera | 2 | 03/01/15 | $519.75 |
INV2 | Installation | 1 | 03/01/15 | $693.00 |
INV2 | Installation | 1 | 03/01/15 | $1,107.54 |
INV2 | BLR | 2 | 03/01/15 | $1,062.60 |
INV2 | Traffic | 1 | 03/01/15 | $984.69 |
<colgroup><col width="158" span="5" style="width: 119pt;"></colgroup><tbody>
</tbody>
Where the final sheet should look something like this after all analysis:
Column A Invoice | Column B Product Line | Column C Random | Column D Date | Column E Amount |
INV1 | Tire | 1 | 03/01/15 | $972.93 |
INV1 | Camera | 2 | 03/01/15 | $519.75 |
INV2 | Installation | 1 | 03/01/15 | $1800.54 |
INV2 | BLR | 2 | 03/01/15 | $1,062.60 |
INV2 | Traffic | 1 | 03/01/15 | $984.69 |
<colgroup><col width="158" span="5" style="width: 119pt;"></colgroup><tbody>
</tbody>
Thank you