MATCH BUY/SELL ORDERS

[ADoc01]

Hi all,

I am building a test model and I am having an issue working out which formula is best to use to achieve my aim.

The example is as follows.

There are 3 clients who each will submit a sheet with BUY/SELL instructions with each other corresponding to a particular period (see images).

In a separate tab, I want to be able for Month 1, Month 2 etc. see where the BUY and SELL orders correctly cross.

For example, Month 1 should generate COMPANY A and COMPANY C as a buy/sell match.
Month 2 should generate COMPANY A and COMPANY C as a buy/sell match.

This test will go on to include at least 10 different companies with 20 periods so I am just looking for direction as to where to start. I have flexibility as to how the results are presented but the input method is fixed.

Apologies if this seems like a silly question .. any help to set me on my way and suggestions of how best to do this would be much appreciated.

 

[KRice]

I'm not sure that I'm interpreting the input sheets correctly. You mention that for month 2, A-C would be a buy/sell match? Wouldn't it be A-B instead?

 

[ADoc01]

Hi KRice thanks for responding,

You are correct that's my fault.

Here is a list of outputs I would want to generate from this sample:

Month 1:
COMPANY A vs COMPANY B BUY BUY so no match
COMPANY A vs COMPANY C SELL BUY so MATCH
COMPANY B vs COMPANY C SELL SELL so no match
Month 2:
COMPANY A vs COMPANY B SELL BUY so MATCH
COMPANY A vs COMPANY C SELL SELL so no match
COMPANY B vs COMPANY C BUY BUY so no match
Month 3:
COMPANY A vs COMPANY B SELL SELL so no match
COMPANY A vs COMPANY C BUY SELL so MATCH
COMPANY B vs COMPANY C BUY SELL so MATCH

I am beginning to see I can do this in a long way via INDEXMATCH or VLOOKUPs and then IF & TRUE/FALSE functions, however in reality the number of companies + periods would result in a lot of combinations and I want to know if there is a better and more efficient way of gathering output (I would not necessarily need to see the no match results if it can be avoided).

 

[KRice]

Thanks for the clarification. Is there any type of standardization on the input sheet names? If so, that would facilitate pulling information from them.
For the input sheets, is the name of the company submitting the input always found in cell A1?...and their buy/sell preferences with other companies are always shown in rows 2 and below?

And is it possible/likely that there will be multiple matches for the same company in any given month? For example, company A might be a buy/sell match with C, D, and G in month 1?

 

[ADoc01]

Regarding the Input sheet:
Yes they would be standardized and pulled into Excel daily.
Yes the name of the company in cell A1 and buy sell preferences in rows 2 and below

And correct there could be multiple matches in any given month.