CODEWITHSUNDEEP
vbaexcel
Data Guy
Data Guy

Reputation: 87

Excel VBA data manipulation

My problem might be simple but i have been stuck on it for a while. I have a list of accounts in column B (XXXXXX). Then in column D i have a quantity of contracts traded. In the column E i have the prices of those contracts. The column next to it has the name of the product traded.

The way i get the raw data i sometimes get a trade split in 2 parts. For example i might have on one row for the same (column B) account XXXXXX , QTY(Column D) 2 and Price(column E) 5.23$ and security(column F) NKE. In the next row i have the same account XXXXX , QTY is 3 , Price is same and the security is same. I want something that will loop through the entire data and add the QTY to 5 and keep everything else the same. So at the end i should have one row with with account XXXXXX with QTY 5 Price 5.23$ and Security NKE. I have tried pivot tables but it adds the price of the security to 10.46$ and that messes up calculations. I cannot figure what sort of a macro i should use. Can someone please help. I am posting a picture to help understand the problem. THanks. enter image description here

Upvotes: 0

Views: 163

Answers (3)

Data Guy
Data Guy

Reputation: 87

To the person who posted a macro. Can you please repost? I was just writing a note to you and I see you deleted the comment

Upvotes: 0

Jason Stallard
Jason Stallard

Reputation: 348

A pivot should show you what you want. Don't put Price in VALUES. Account, Security and Price can go in ROWS then Sum of Quantity in VALUES. That should give you the breakdown you're after.

Upvotes: 1

Egan Wolf
Egan Wolf

Reputation: 3573

  1. Choose the most unique column (probably F).
  2. Filter your data by this column.
  3. Make an infinite loop. Inside the loop:
    • declare a variable for row number
    • check if the current row has the same value in column F as the row below
    • if yes, add quantities in first row and delete second row
    • if no, add one to row number and check if there is something in the current row. If no, exit the loop.

Upvotes: 0

Related Questions