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 209View attachment Mr excel.xlsb
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 209View attachment Mr excel.xlsb
