How to get possible pairs from a data

[ThrottleWorks]

Please note, I will be uploading sample files shortly.
Will create dummy files based on the details mentioned below.

Hi,

I have one excel sheet. There may be more than 100 rows in this sheet.
Number of columns are 9.

These are trade submissions. I need to find possible pairs from raw data.
If I have data for 100 rows, then maximum possible pairs will be 50.
It can be less than 50 also but can never exceed 50.

Column Ref will be as follows;
Unique ID, MyRef, CPRef, VD, Bcurr, BAmt, Scurr, SAmt, and Amt.

Unique ID

MyRef

CPRef

VD

Bcurr

BAmt

Scurr

SAmt

Amt

ABC123

MyRef1

CPRef1

18/05/2016

CHF

100

USD

200

1.1000000000

ABC124

CPRef1

MyRef1

18/05/2016

USD

200

CHF

100

1.1100000000

If we take the above mentioned example, this is one possible pair I have created manually.
Unique ID for all the 100 records will be different, in other words there will be no duplicate in this column.

MyRef and CPRef, both will have duplicate value.
Both these Refs will be present any either column B or C.

I need to find possible pair for ‘MyRef’.
First row is my submission, second row is CP’s submission.

I need to find possible pair for my submission against CP’s submission.

For example purpose this data is in sequence. Original data is not in sequence.
I need to match 8 fields (apart from Unique ID) from my submission against CP’s submission.
If I get true for any of the 6 fields, I will highlight it as a possible pair.

I am not trying for exact answers, can you please help me with guidelines about how I should proceed.
If you can explain me what method I should use it will be very helpful for me.

If in any pair, any of the 6 fields are matching on both the side then I will match it.
Please note, in any pair, My Ref and CP Ref will not be in same column.

For example, B1 = My Ref123 C1 = CP Ref123 then a possible pair will always have
My Ref 123 in C2 and CP Ref 123 in B2.

Sorry for confusing, please give me some time to upload dummy files.

 

[Marc L]

Hi !

You can first search a concatenation of CPRef & MyRef …
Or a concatenation of the 6 fields …
​

 

[Marc L]

You even do not need a code just using formulas in two helper columns :
one for the concatenation, one for OFFSET & MATCH functions for example …
​

 

[ThrottleWorks]

Hi @Marc L sir vous êtes genial ! Thanks a lot.

Sometimes I complicate things without reason.

PS - I hope translation is right.

 

[Marc L]

Thanks !

Translation is right but I'm not such a genius, it's just Excel basics …
​