Sort by Stock Name then list by Oppsite Transactions

**SMJE** · 01-15-2011, 01:40 PM

I need to create a macro which will sort data, first by Stock Name. Then, for each Stock Name, rows which are opposite transactions to be listed first. Opposite transactions are those which have the same stock name, same quantity but one is delivery and the other is receipt.

So, in the attached spreadsheet, the data on the left should be sorted to show the data on the right.

Can any please help me?

**broro183** · 01-15-2011, 08:40 PM

hi SMJE,

Welcome to the Forum

I'm (perhaps unnecessarily) concerned about mismatches...
Does your real data have any date fields to help ensure that the correct stocks are being matched?
Or is the stock name more of a "unique identifier" in your real data?
Edit: or is your sample reflective of your actual data & there are no more fields that should match?

Rob

**MarvinP** · 01-15-2011, 09:00 PM

Hi SMJE and welcome to the forum,

Find the attached where there are 4 possible ways to deal with your data. I'm sorry but I like Pivot Tables.

See the attached. I like the one with a NegDir column added to your data and an easy Pivot from it.

**broro183** · 01-15-2011, 10:42 PM

hi MarvinP,

I like pt's too but in this case, I'm not sure if it's quite what the OP wants/needs. I maybe wrong but I think the OP wants to see all the detail & individual "matches" rather than a net result. I guess we shall see...

Rob

**Leith Ross** · 01-16-2011, 05:14 AM

Hello SMJE,

Welcome to the Forum!

In my experience Shipping and Receiving operations use unique identifiers for tracking the stock like Purchase Order numbers or SKUs. The following macro is based on this assumption. A button has been added to "Sheet1" to run the macro. The results appear on "Sheet2". The attached workbook has both the macro and button added.

Sort Stock Data Macro

Please Login or Register  to view this content.

**SMJE** · 01-17-2011, 04:55 PM

Originally Posted by broro183

hi SMJE,

Welcome to the Forum

I'm (perhaps unnecessarily) concerned about mismatches...
Does your real data have any date fields to help ensure that the correct stocks are being matched?
Or is the stock name more of a "unique identifier" in your real data?
Edit: or is your sample reflective of your actual data & there are no more fields that should match?

Rob

Thanks all - The data is a daily report, so yes has a date field, but all will be identical.
Yes the stock code is a unique identifier, there are other fields, but the fields which vary are: Stock code, direction, quantity.

There is no need to worry about whether they are 'true matches' since we need matches to notify the broker who they need to deliver/receive to, and we only require that they have the same quantity.

broro183 - I would prefer not to use a Pivot table.

Leith Ross - thanks for the code - yes indeed it does bring opposite transactions to the top of the list, however the rows listed afterwards are not in the order I desire.

I've attached a spreadsheet to show in more detail what I'm aiming for! TIA

**broro183** · 01-18-2011, 06:39 PM

hi everyone,

SMJE,

There is no need to worry about whether they are 'true matches' since we need matches to notify the broker who they need to deliver/receive to, and we only require that they have the same quantity.

Wow!
I'm pleased the stock code is a unique identifier, but in my past (Warehouse related) job, I would have had my backside booted if I had matched movements on the basis of quantity. Mind you, it was food processing & did require a high level of tracability.

Ignoring the "netting" effect caused by MarvinP's example pivot tables so far, why would you prefer not to use pivot tables?

however the rows listed afterwards are not in the order I desire.

I may be going a bit far here (edit: because you've said thanks & tia etc

), but, I think a "please" would fit into the above quite nicely

Leith (or others),
I'll be interested to see what you come up with. All I can think of is using a "Do Until" loop, but I haven't got any further than that!

Rob

**MarvinP** · 01-18-2011, 07:25 PM

Hey Rob,
When I first read this question I thought it was Stock Certificates or Stock Symbols he/she was dealing with, like MSFT or IBM. I've had a lot of practice, at tax time, trying to see why shares of stock don't add up. Pivot Tables have tamed these problems.

Then Leith thought it was a SKU stock problem. Then the word "broker" was used. Then you used the term Warehouse, back to the idea of SKU's and what's in the store. I'm still a little clueless on what the real problem is.

**broro183** · 01-19-2011, 06:57 PM

lol!

Good point MarvinP - at least you've taken your blinkers off to consider the other possibility of the problem relating to SKU's. If you hadn't posted, I wouldn't have even considered sharemarket style Stock Certificates etc & I would have just carried on, in ignorant bliss, thinking "that's odd to use the word 'Broker'".

I guess we are all shaped by where we've been & what we've seen...

BUT, I'll try & take my blinkers off.

Well, hopefully the OP can give us the context - it may even influence our thoughts on the best solution.

SMJE,
Sorry, I haven't had a go at the coding for the latest requirements & am off to bed soon. I've got footy tomorrow, so it's unlikely to happen then but I'll try & put something together this weekend. Hopefully someone else can come up with a nice solution for you before then...

Rob