Macro to roll up common rows.

ssully87 · Nov 26, 2014

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.

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

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

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

Let me know if you need any additional information, I would have uploaded the file but i'm not sure how to.

bobkuhn · Nov 26, 2014

Sounds like themain request is to speed up your macro.<o

></o

>
<o

> </o

>
I don't know ofany built-in functions that can be used to process the data.<o

></o

>
<o

> </o

>
Turning offdisplay updating will speed things up:<o

></o

>

Code:

[SIZE=3][FONT=Times New Roman][COLOR=#000000]Application.ScreenUpdating = False<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]Application.ScreenUpdating = True<o:p></o:p>[/COLOR][/FONT][/SIZE]

<o

> </o

>
The other speed up I've used is to copy everything into anarray, do all the processing within the array and then write the array backout. In this case you may want to useone array to hold the original data and a second array to hold the results. Here's snippets of code that I've used.<o

></o

>
<o

> </o

>
Bob<o

></o

>
<o

> </o

>

Code:

[SIZE=3][FONT=Times New Roman][COLOR=#000000]Dim Sheet_original As Variant<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]Dim Sheet_final As Variant<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]Sheet_original = Range(Cells(1, 1), Cells(10000,50)).Value   ' copy into array<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]original_index_0 = 0<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]Do While Sheet_original(original_index_0, 0, 0, 0, 0, …) IsNot Nothing ' could be <> ""<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]  Sheet_final(final_index_0, final_index_1, final_index_2, …) =Sheet_original(original_index_0, original_index_1, original_index_2, …)<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]original_index_0 = original_index_0 + 1<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000]Loop<o:p></o:p>[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][FONT=Times New Roman][COLOR=#000000] Sheets("destination_sheet").Select<o:p></o:p>[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=#000000][FONT="Times New Roman"]Range(Cells(1, 1), Cells(10000, 50)).Value =Sheet_final  'paste array intospreadsheet[/FONT][/COLOR][/FONT][/SIZE]

ssully87 · Nov 27, 2014

We'll the main problem is i don't know how to write a macro to accomplish the task at hand. I have written quite a few macros based of recording tasks first, but nothing this extensive.

From what i can tell the macro needs to do the following.

insert 6 columns after % and name the headings producer 2 comission 2 % #2 etc...

find A2 in the invoice column
if the A3 is the same value
then copy B3 and paste in E2.
Then move to A4 if the value = A2
then copy B4 and paste in E2.
Then move to A5 if the value = A2
then copy B5 and paste in E2.

'it would repeat this process till the value in column A no longer matched and would move to the next value or invoice.
Then it would do this process for column C,and D respectively...so on and so forth till the all the data was rolled up into one row.

I would be able to figure out the macro for the totaling of the amount but everything else is beyond me.
I hope this helps explain my situation more.

bobkuhn · Nov 28, 2014

I suggest you start playing with some macros and looking at examples of VBA code. Nothing like experience to teach you how to do things.

Here's a macro that will do what you want. It's pretty straight forward and shows how to do one type of basic looping.

Code:

Sub report_reformat()

     source_sheet = ActiveSheet.name
     Sheets.Add
     ActiveSheet.name = "result_sheet"
     destination_sheet = ActiveSheet.name
     
' init header row
     Cells(1, 1) = "Invoice"
     Cells(1, 2) = "Amount"
     Cells(1, 3) = "PRD1"
     Cells(1, 4) = "Com1"
     Cells(1, 5) = "PRD 1 %"
     Cells(1, 6) = "PRD2"
     Cells(1, 7) = "Com2"
     Cells(1, 8) = "PRD 2 %"
     Cells(1, 9) = "PRD3"
     Cells(1, 10) = "Com3"
     Cells(1, 11) = "PRD 3 %"
     Cells(1, 12) = "Total Amount"

' make pretty
     Columns("L:L").ColumnWidth = 12.43
     Range("D:D,B:B,G:G,J:J,L:L").Select
     Selection.NumberFormat = "#,##0.00" 
     
     source_row = 2
     destination_row = 2
     
     current_invoice_num = Sheets(source_sheet).Cells(2, 1)
     
     Do While current_invoice_num <> ""
       Cells(destination_row, 1) = current_invoice_num
       Cells(destination_row, 2) = Sheets(source_sheet).Cells(source_row, 5)  'copy amount over
       Cells(destination_row, 12) = Sheets(source_sheet).Cells(source_row, 5)  ' the two amounts are alwats the same
       PRD_num = 1
      
' copy PRD data over
       Do While current_invoice_num = Sheets(source_sheet).Cells(source_row, 1)  ' only move PRD data if in same invoice
         Cells(destination_row, 3 * PRD_num) = Sheets(source_sheet).Cells(source_row, 2)
         Cells(destination_row, 3 * PRD_num + 1) = Sheets(source_sheet).Cells(source_row, 3)
         Cells(destination_row, 3 * PRD_num + 2) = Sheets(source_sheet).Cells(source_row, 4)
         PRD_num = PRD_num + 1
         source_row = source_row + 1
       Loop

' done with this invoice
       destination_row = destination_row + 1
       current_invoice_num = Sheets(source_sheet).Cells(source_row, 1)
     Loop

End Sub

Here's a faster way of doing it. It reads the source sheet into an array, copies the data do a destination array and then writes the destination array into a new sheet.

You'll notice the indices are different between the two. That's because the spreadsheet rows/columns start at 1 while the array indices start at 0.

Bob

Code:

Sub report_reformat_array()

Dim source_array As Variant
Dim destination_array As Variant
Dim temp As Variant

     source_array = Range(Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Value  ' read in source sheet
     
     max_x = UBound(source_array, 1) - LBound(source_array, 1) + 1
     ReDim destination_array(max_x, 12)   ' make destination array the same size - that way we know for sure that it's big enough
     
     
     source_row = 2
     destination_row = 0 'array index starts at 0
     
     current_invoice_num = source_array(2, 1)
     
     Do While current_invoice_num <> ""
       destination_array(destination_row, 0) = current_invoice_num
       destination_array(destination_row, 1) = source_array(source_row, 5)  'copy amount over
       destination_array(destination_row, 11) = source_array(source_row, 5)  ' the two amounts are alwats the same
       PRD_num = 1
      
' copy PRD data over
       Do While current_invoice_num = source_array(source_row, 1)  ' only move PRD data if in same invoice
         destination_array(destination_row, 3 * PRD_num - 1) = source_array(source_row, 2)
         destination_array(destination_row, 3 * PRD_num) = source_array(source_row, 3)
         destination_array(destination_row, 3 * PRD_num + 1) = source_array(source_row, 4)
         PRD_num = PRD_num + 1
         source_row = source_row + 1
       Loop

' done with this invoice
       destination_row = destination_row + 1
       current_invoice_num = source_array(source_row, 1)
     Loop

     Sheets.Add
     
' init header row
     Cells(1, 1) = "Invoice"
     Cells(1, 2) = "Amount"
     Cells(1, 3) = "PRD1"
     Cells(1, 4) = "Com1"
     Cells(1, 5) = "PRD 1 %"
     Cells(1, 6) = "PRD2"
     Cells(1, 7) = "Com2"
     Cells(1, 8) = "PRD 2 %"
     Cells(1, 9) = "PRD3"
     Cells(1, 10) = "Com3"
     Cells(1, 11) = "PRD 3 %"
     Cells(1, 12) = "Total Amount"

' make pretty
     Columns("L:L").ColumnWidth = 12.43
     Range("D:D,B:B,G:G,J:J,L:L").Select
     Selection.NumberFormat = "#,##0.00"


     Range(Cells(2, 1), Cells(destination_row + 1, 12)).Value = destination_array 'paste array into spreadsheet
     
     Cells(1, 1).Select

End Sub

ssully87 · Dec 1, 2014

Everything seems to be working great, i have been able to adjust the columns to what mjy actual report has. Although iv'e run into an issue. The rule set up was to not copy over a producer if the amount is repeated. sometimes and invoice is back out and re done. this means the invoice may have 24 then -24 then 24 again. How would the code be adjusted to only take the absolute value and not tell the difference between + and - numbers?

ssully87 · Dec 1, 2014

For an example look at the below.

1768	prod13	-2.84	10	284	-284
1768	BRB	0	0	0	0
1768	prod13	2.84	10	284	284
1768	BRB	0	0	0	0

bobkuhn · Dec 1, 2014

What does the final result look like?

Will all the invoice rows be adjacent or will I need to do some type of sort/search?

Bob

ssully87 · Dec 2, 2014

bobkuhn said:
What does the final result look like?

Will all the invoice rows be adjacent or will I need to do some type of sort/search?

Bob

the result would be

1768

prod13

-2.84

10

BRB

0

with a total of 0.
i thought i would be able to figure out how to total up the vlues of amounts but i'm having trouble with that as well

Macro to roll up common rows.

ssully87

New Member

Excel Facts

bobkuhn

New Member

ssully87

New Member

bobkuhn

New Member

ssully87

New Member

ssully87

New Member

bobkuhn

New Member

ssully87

New Member

Similar threads

Forum statistics

Share this page

Macro to roll up common rows.

New Member

Excel Facts

New Member

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock