Aggregating multiple rows of data using vba code

[raj saha]

Hello everyone. I am in the process of building an options portfolio manager. i am trying to merge and aggregate multiple rows of similar data based on specific criteria. if information in columns b, c,e,f,g are the same then combine into a single row. also only columns d,m,n,o,p,q,r,s,u should be added together. once its aggregated all the rows i would like to replace all the data on that sheet with new merged rows. i have attached a spreadsheet that should make it easier to understand what i am trying to accomplish. thank you for all your help and advice.

 

[Marc L]

Hi !

Try RemoveDuplicates method or directly from Excel …
​

 

[raj saha]

thanks, but i want to aggregate and sum up the duplicates into one line item as opposed to deleting them.

 

[Marc L]

You can first calculate before removing duplicates …
​

 

[Chihiro]

Try using Pivot Table?

Only issue is tjat Qty must be in VALUES field since there's calculation being done on it, and must occur to right of "Prv. Settle".

 

[raj saha]

chichiro,

thank you very much for the suggestion. how did you get the pivot table to look the way it does. i cant seem to be able to replicate the same format that you have.

thanks.

 

[Chihiro]

Go to PivotTable Options->"Display" tab, check "Classic PviotTable layout"


For each ROWS (Row Labels), ungroup them and right click on the label and remove "Subtotal".


Then go to "Field Setting" of each ROWS and go to "Layout & Print" tab, check "Repeat item labels".

 

[raj saha]

thank you very much.