Consol Duplicate lines with SUM with given criteria

[Sriram170]

I am in a project where i have to consol the no of multiple transactions and sum the amount with given criteria below is the example of the data. I need them to add up the amount as single line when " Profit Ctr, Partner PC, Account and WBS element are same. Please provide a workaround with VBA where i can add a criteria or remove a criteria in future if required.

thanks in advance


RefDoc.No. Doc.line Time Profit Ctr Partner PC Period Account WBS Element Entry Date Amount
1 3 13:21:32 NL22278300 2 666666 IN.007.007 07/04/2016 100,00
2 4 13:21:32 NL22278300 2 666666 IN.007.007 07/04/2016 101,00
3 1 11:57:29 NL22251400 NL22251400 2 666666 IN.007.008 07/04/2016 102,00
4 2 11:57:29 NL22251400 NL22251400 3 666666 IN.007.008 07/04/2016 103,00
5 1 11:57:28 NL22279900 NL22279900 3 666667 IN.007.008 07/04/2016 104,00
5 2 11:57:28 NL22279900 NL22279900 3 777777 IN.007.007 07/04/2016 105,00
6 1 11:57:27 NL22274400 NL22274400 3 777777 IN.007.009 07/04/2016 106,00
6 2 11:57:27 NL22274400 NL22274400 4 777777 IN.007.009 07/04/2016 107,00
7 3 11:57:27 NL22274400 NL22274400 4 777777 IN.007.009 07/04/2016 108,00
8 4 11:57:27 NL22274400 NL22274400 4 777777 IN.007.009 07/04/2016 109,00
8 5 11:57:27 NL22274400 NL22274400 3 777777 IN.007.007 07/04/2016 110,00



the below should be the result, but when you use pivot it is not possible because the ref doc is uniqe and date is unique then time is unique


RefDoc.No. Doc.line Time Profit Ctr Partner PC Period Account WBS Element Entry Date Amount
3 1 11:57:29 NL22251400 NL22251400 2 666666 IN.007.008 42467 205
6 1 11:57:27 NL22274400 NL22274400 3 777777 IN.007.009 07/04/2016 540
1 3 13:21:32 NL22278300 2 666666 IN.007.007 42467 201
5 1 11:57:28 NL22279900 NL22279900 3 666667 IN.007.008 42467 209

 

[p45cal]

The amount sums in your attachment (205,540,201,209) work if only Profit Ctr & Partner PC are similar; the 209 is made up of different Accounts and different WBS Elements. The 540 is made up of different WBS Elements.

Give us some good data and good corresponding sample output, then we can have a go at discovering which row(s) the RefDoc No., doc.line, Time etc. should come from after the consolidation.

ps. I take it from the attachment name that you've cross posted to MrExcel?
If so then perhaps you should supply a link?