Multiple criteria Sum, Merge & Delete Rows

[arnorian]

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

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

 

[Mr_Olaf]

Hello arnorian,

I think, when you need to sumarize data, pivot table is the simplest solution. From your data, I created pivot table that looks like this:

Invoice	Product Line	Random	Date	Sum of Amount
INV1	Camera	2	3.1.2015	$ 519,75
INV1	Tire	1	3.1.2015	$1.072,93
INV2	BLR	2	3.1.2015	$1.062,60
INV2	Installation	1	3.1.2015	$1.800,54
INV2	Traffic	1	3.1.2015	$984,69
Grand Total				$5.440,51

<tbody>
</tbody>

 

[arnorian]

Hi Mr_Olaf,

I have tried Pivot table first actually. Problem with Pivot is there are dozens of product line categories which makes Pivot hard to read and summarize. Additionally, when I'm analyzing data by customer/invoice for other calculations it would be very difficult. I think some sort of VBA or Macro code would be necesary.

 

[SpillerBD]

Hi Mr_Olaf,

I have tried Pivot table first actually. Problem with Pivot is there are dozens of product line categories which makes Pivot hard to read and summarize. Additionally, when I'm analyzing data by customer/invoice for other calculations it would be very difficult. I think some sort of VBA or Macro code would be necesary.

I'm with the Olaf, use a Pivot Table. Your difficulty with Pivot Table may be in understanding the multitude of formatting options you have. Plus the various ways of applying Filters to get just the data you are looking for. Slicers are another method of filtering pivot tables, just more graphical.
You can easily switch from a customer centric Pivot Table to a product centric table and include Quantities and separate dollar summations. Subtotals can be included or omitted at any level. Then you could throw those summaries into a chart too.

 

[Mr_Olaf]

Have you tried to reduce output data from Pivot table by adding report filters and slicers. You can make many pivot tabels from the same pivot cahe and create different results from the same data. Refreshing pivot cache will refresh all pivot tables. Macro can help you not to doing it manualy.
Any kind of VBA solution can' t be more clear then results obtained from Pivot table, because you can make them as simple, as you need.
Maybe if you can give me more examples what you would like to get from dour data.