consolidating table data

I use a multi-tab workbook which calculates stock trades. One of its tabs is a "trade list" which looks like the first tab on the attached workbook.

The first challenge is to remove rows which have no activity. I think I could probably fix that problem using a pivot table (although I've never mastered these. If the data in my main workbook changes, will the pivot table change accordingly?)

The more complex challenge for me is that I need to reformat the trade list so that it looks like the one on the second tab. In the second tab, a single row will include both the opening and closing info. for each trade. (e.g. date, price, ticker, volume, profit)

Sometimes, the trading strategy will open two trades on different dates, then close both of them out on another day. This is what you'll see with the first trade.

So... How do I match a trade's closing data to a trade's opening data so that it looks like the second tab? (I've manually plugged in about 6 trades so you can see where I got the data from on the first tab). Could I assign a unique number to each trade as a "helper"? (and how would I do that?)

Note that the example worksheet uses color to indicate each full trade.

Please let me know if I need to clarify anything! I very much appreciate your help.

EDIT: I've marked this thread as "solved" because I'm going to post a similar thread in the commercial forum.

How many rows (maximum) are there in raw data?

If it's the same workbook as the one referred to in other threads, then it's large and there are already speed issues, so a formula solution probably won't be appropriate.

You can try this code to rearange the data in a table.

After that you can use (e.g.) a pivot table to analyse the data.

Please Login or Register  to view this content.

I can do this with a formula easily enough... but it's all down to array formulae, so it probably won't work for huge datasets.

Edit: scrub that idea... fatally flawed...

Hi everyone, thanks for looking at this. Oeldere, I'll try your code and reply once I've run it.

I think its ok to leave this thread open until I've had a chance to test the code?

PS Glenn, I'll have multiple trade lists, and the number of rows will vary probably between 700 and 2500.

Again, thank you!