Hello, I'm trying to write a macro to rearrange a table into a usable pivot table.
please take a look at the attachment for example.
table.png
The table on the left is the raw data which will be pasted in the sheet, table on the right the is the sorted data after the macro is run (in a new sheet, not replacing original data).
The new table is sorting on round trips of quantities.
for example the first row checks item is apple, 200 bought.
Then check next row if same amount is sold (quantity is now 0), then print price sold price beside bought price
move on to next row.
*Notice the second transaction sums the quantities, and averages the prices.
any help with how to do this would be appreciated. I feel overwhelmed that I need to have multiple indexes and offsets to print the new table, and am not too familiar with arrays yet.
thank you
edit:attached a sample worksheet
edit2:updated sample worksheet
Bookmarks