Hello,
I'm trying to write a Macro but i have not mastered functions and what not so I am looking for a little help. I have the below information. My real report does not look like this, however to maintain my companies privacy I have only added what needs to be adjusted.
I would write simple macros, however the report is tens of thousands of lines and without functions it will take a long time to run the macro. Also i'm not sure how I would accomplish this with simple macros as the data is not always consistent. Sometimes one invoice could have one line, and sometimes one invoice could have up to 9 lines. In any given circumstance there will be no more than 3 Producers or PRD.
<tbody>
</tbody>
I am trying to roll up the rows so that every invoice only has on line item, but still shows all producers and amounts on the invoice. Then add a column to show the total amount of the invoice like the below.
<tbody>
</tbody>
Then it would remove all the duplicate invoice rows to only show the below.
<tbody>
</tbody>
Let me know if you need any additional information, I would have uploaded the file but i'm not sure how to.
I'm trying to write a Macro but i have not mastered functions and what not so I am looking for a little help. I have the below information. My real report does not look like this, however to maintain my companies privacy I have only added what needs to be adjusted.
I would write simple macros, however the report is tens of thousands of lines and without functions it will take a long time to run the macro. Also i'm not sure how I would accomplish this with simple macros as the data is not always consistent. Sometimes one invoice could have one line, and sometimes one invoice could have up to 9 lines. In any given circumstance there will be no more than 3 Producers or PRD.
Invoice | Producer | Commision | % | Amount |
B12659 | prod12 | 78.06 | 20 | 2,602.10 |
B12659 | prod11 | 0 | 0 | 0 |
B12659 | GIVEN | 0 | 0 | 0 |
B12660 | prod12 | 169.53 | 20 | 16,953.10 |
B12660 | prod11 | 0 | 0 | 0 |
B12660 | GIVEN | 0 | 0 | 0 |
A147044 | prod3 | 147.5 | 25 | 6,941.00 |
A147044 | prod1 | 0 | 0 | 0 |
C12850 | prod10 | 196.03 | 15 | 26,138.08 |
C12850 | prod9 | 130.69 | 10 | 0 |
C12855 | prod10 | 5.48 | 15 | 366.38 |
C12855 | prod9 | 3.65 | 10 | 0 |
C12856 | prod10 | 31.88 | 15 | 2,125.47 |
C12856 | prod9 | 21.25 | 10 | 0 |
C12865 | prod7 | 66 | 10 | 660 |
C12865 | prod8 | 99 | 15 | 0 |
C12866 | prod7 | 4.46 | 10 | 446.57 |
C12866 | prod8 | 6.69 | 15 | 0 |
C12869 | prod7 | 1.01 | 10 | 101.65 |
C12869 | prod8 | 1.52 | 15 | 0 |
C12970 | HOUSE | 0 | 0 | 0 |
C12970 | prod7 | 0 | 0 | 0 |
B12970 | HOUSE | 0 | 0 | 715.28 |
B12970 | prod7 | 0 | 0 | 0 |
B13014 | prod6 | 28.82 | 20 | 960.62 |
B13014 | GIVEN | 0 | 0 | 0 |
C13016 | prod6 | 18.2 | 20 | 758.26 |
C13016 | GIVEN | 0 | 0 | 0 |
C13166 | prod3 | 78.77 | 15 | 4,375.84 |
C13166 | prod5 | 0 | 0 | 0 |
C13166 | prod1 | 0 | 0 | 0 |
B13167 | prod3 | 101.29 | 15 | 5,627.18 |
B13167 | prod | 0 | 0 | 0 |
B13167 | prod1 | 0 | 0 | 0 |
A13177 | prod2 | 23.14 | 10 | 2,012.00 |
A13177 | prod3 | 0 | 0 | 0 |
<tbody>
</tbody>
I am trying to roll up the rows so that every invoice only has on line item, but still shows all producers and amounts on the invoice. Then add a column to show the total amount of the invoice like the below.
Invoice | Amount | PRD1 | Com1 | PRD 1 % | PRD2 | COM2 | PRD 2 % | PRD3 | COM3 | PRD 3 % | Total Amount |
B12659 | 2,602.10 | prod12 | 78.06 | 20 | prod11 | 0 | 0 | GIVEN | 0 | 0 | 2,602.10 |
B12659 | 0 | prod11 | 0 | 0 | |||||||
B12659 | 0 | GIVEN | 0 | 0 | |||||||
B12660 | 16,953.10 | prod12 | 169.53 | 20 | prod11 | 0 | 0 | GIVEN | 0 | 0 | 16,953.10 |
B12660 | 0 | prod11 | 0 | 0 | |||||||
B12660 | 0 | GIVEN | 0 | 0 | |||||||
A147044 | 6,941.00 | prod3 | 147.5 | 25 | prod1 | 0 | 0 | 6,941.00 | |||
A147044 | 0 | prod1 | 0 | 0 | |||||||
C12850 | 26,138.08 | prod10 | 196.03 | 15 | prod9 | 130.7 | 10 | 26,138.08 | |||
C12850 | 0 | prod9 | 130.69 | 10 | |||||||
C12855 | 366.38 | prod10 | 5.48 | 15 | prod9 | 3.65 | 10 | 366.38 | |||
C12855 | 0 | prod9 | 3.65 | 10 | |||||||
C12856 | 2,125.47 | prod10 | 31.88 | 15 | prod9 | 21.25 | 10 | 2,125.47 | |||
C12856 | 0 | prod9 | 21.25 | 10 | |||||||
C12865 | 660 | prod7 | 66 | 10 | prod8 | 99 | 15 | 660.00 | |||
C12865 | 0 | prod8 | 99 | 15 | |||||||
C12866 | 446.57 | prod7 | 4.46 | 10 | prod8 | 6.69 | 15 | 446.57 | |||
C12866 | 0 | prod8 | 6.69 | 15 | |||||||
C12869 | 101.65 | prod7 | 1.01 | 10 | prod8 | 1.52 | 15 | 101.65 | |||
C12869 | 0 | prod8 | 1.52 | 15 | |||||||
C12970 | 0 | HOUSE | 0 | 0 | prod7 | 0 | 0 | 0.00 | |||
C12970 | 0 | prod7 | 0 | 0 | |||||||
B12970 | 715.28 | HOUSE | 0 | 0 | prod7 | 0 | 0 | 715.28 | |||
B12970 | 0 | prod7 | 0 | 0 | |||||||
B13014 | 960.62 | prod6 | 28.82 | 20 | GIVEN | 0 | 0 | 960.62 | |||
B13014 | 0 | GIVEN | 0 | 0 | |||||||
C13016 | 758.26 | prod6 | 18.2 | 20 | GIVEN | 0 | 0 | 758.26 | |||
C13016 | 0 | GIVEN | 0 | 0 | |||||||
C13166 | 4,375.84 | prod3 | 78.77 | 15 | prod5 | 0 | 0 | prod1 | 0 | 0 | 4,375.84 |
C13166 | 0 | prod5 | 0 | 0 | |||||||
C13166 | 0 | prod1 | 0 | 0 | |||||||
B13167 | 5,627.18 | prod3 | 101.29 | 15 | prod | 0 | 0 | prod1 | 0 | 0 | 5,627.18 |
B13167 | 0 | prod | 0 | 0 | |||||||
B13167 | 0 | prod1 | 0 | 0 | |||||||
A13177 | 2,012.00 | prod2 | 23.14 | 10 | prod3 | 0 | 0 | 2,012.00 | |||
A13177 | 0 | prod3 | 0 | 0 | |||||||
1803 | -1,335.45 | BRB | -267.09 | 20 | -8,903.03 | 0 | GIVEN | 0 | 0 | 0 | 0.00 |
1803 | 0 | GIVEN | 0 | 0 | 0 | ||||||
1803 | 1,335.45 | BRB | 267.09 | 20 | 8,903.03 | ||||||
1803 | 0 | GIVEN | 0 | 0 | 0 |
<tbody>
</tbody>
Then it would remove all the duplicate invoice rows to only show the below.
Invoice | Amount | PRD1 | Com1 | PRD 1 % | PRD2 | COM2 | PRD 2 % | PRD3 | COM3 | PRD 3 % | Total Amount |
B12659 | 2,602.10 | prod12 | 78.06 | 20 | prod11 | 0 | 0 | GIVEN | 0 | 0 | 2602.1 |
B12660 | 16,953.10 | prod12 | 169.53 | 20 | prod11 | 0 | 0 | GIVEN | 0 | 0 | 16953.1 |
A147044 | 6,941.00 | prod3 | 147.5 | 25 | prod1 | 0 | 0 | 6941 | |||
C12850 | 26,138.08 | prod10 | 196.03 | 15 | prod9 | 130.7 | 10 | 26138.08 | |||
C12855 | 366.38 | prod10 | 5.48 | 15 | prod9 | 3.65 | 10 | 366.38 | |||
C12856 | 2,125.47 | prod10 | 31.88 | 15 | prod9 | 21.25 | 10 | 2125.47 | |||
C12865 | 660 | prod7 | 66 | 10 | prod8 | 99 | 15 | 660 | |||
C12866 | 446.57 | prod7 | 4.46 | 10 | prod8 | 6.69 | 15 | 446.57 | |||
C12869 | 101.65 | prod7 | 1.01 | 10 | prod8 | 1.52 | 15 | 101.65 | |||
C12970 | 0 | HOUSE | 0 | 0 | prod7 | 0 | 0 | 0 | |||
B12970 | 715.28 | HOUSE | 0 | 0 | prod7 | 0 | 0 | 715.28 | |||
B13014 | 960.62 | prod6 | 28.82 | 20 | GIVEN | 0 | 0 | 960.62 | |||
C13016 | 758.26 | prod6 | 18.2 | 20 | GIVEN | 0 | 0 | 758.26 | |||
C13166 | 4,375.84 | prod3 | 78.77 | 15 | prod5 | 0 | 0 | prod1 | 0 | 0 | 4375.84 |
B13167 | 5,627.18 | prod3 | 101.29 | 15 | prod | 0 | 0 | prod1 | 0 | 0 | 5627.18 |
A13177 | 2,012.00 | prod2 | 23.14 | 10 | prod3 | 0 | 0 | 2012 | |||
1803 | -1,335.45 | BRB | -267.09 | 20 | -8,903.03 | 0 | GIVEN | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
Let me know if you need any additional information, I would have uploaded the file but i'm not sure how to.