How to find best possible pairs.

[ThrottleWorks]

Hi everyone,

Hope you are enjoying your Sunday !

I have a file. This file has around 20 columns.
Number of rows are not fixed. From these 20 columns only 9 columns are importance from coding aspect.

That is why my sample book consists only those columns.
Please refer to Input sheet for dummy data, output sheet has possible answer.
Each row represent a submission. This submission can be from my side or from opposite side.

Column A has unique identifier. This will be never repeated.
Refs in column B and C can be present multiple times.

In the attached example, row 2 and row 5 are best possible pairs.
In my example, B2 = C5 and C2 = B5, this is the first condition. If these are matching then only I can proceed. Else cannot be considered as a pair.
In short, both refs has be a match.

For the same reason, row 2 and row 9 cannot be a match cause, C2 <> B9.

Now coming back to the matched pair, once we have match ref, we will proceed.
D2 = E5, E2 = D5, F2 = G5, H2 = H5

Only un-matched are, G2 (200) <> F5 (205) and I2 (1.001) and I5 (1.003).
If you check row, 6, only difference is F6 (210).

Since the difference between G2 and F5 is less than G2 and F6, I will consider row 2 and 5 as a pair.

Same case goes for row 12 and 13, there is difference between S Currency and Rate, however B Amt and S Amt are perfect match.

Though row 13 and 14 are matching on everything except S Amt, we will not consider these for pairing against row 12.

I tried a macro, but it turned out to be spaghetti code ! cannot post now. On a different machine.

Tried various concats and combinations but somehow I am not able to do it.
Can anyone please help me in this.

 

[ThrottleWorks]

In real scenario, my ref can be present 10 times in a single file.
Out of these 10 rows, opposite refs can be as many as 6 (4 duplicate opposite refs).

When we reconcile B Amt V/s S Amt, we need to pick pair which has smallest difference.
However we can not match if both, F2 <> G5 and G2 <> F5 (please see first attached file for row reference).

My questions mentioned below were regarding to this problem only.
I guess, I need to use 14 different combinations to get the pairs.
Please see attached file.

http://forum.chandoo.org/threads/how-many-combinations-i-would-require.30638/

http://forum.chandoo.org/threads/how-to-get-possible-pairs-from-a-data.29509/

 

[p45cal]

I haven't got a solution for you as I'm still exploring but thought I'd share what I have so far. The attached is your file with:
1. An advanced filter. It is updated with a macro which is triggered by a double-click in any of the rows within your table at the top. It uses the doubleclicked-in row to determine which row it looks for pairs for. If you successfully double-click in a row, that will be confirmed by the UR number appearing in cell A28. Below that should appear a list (the advanced filter) of eligible rows to pair with:

A formula or two work out where to put an asterisk in the column to the right of this new table, being the smallest difference in the B/S Amt columns signalling the perhaps best choice to make.
Currently the advanced filter handles only the cross-matching of My ref./Their ref., the corss matching of currencies and ensures at least one side of B/S Amt columns cross-match.
2. Double-click a cell with an asterisk in (or any cell above/below it for that matter) and information gets added in column J of the top table showing the pair you selected as the best pair. Information appearing in column J of the top table prevents that row showing up in subsequent double-clicks in the top table. You have to manually delete cells in column J to make those rows eligible. Alternatively, the macro can be tweaked to ignore column J altogether; there is a comment in the code indicating how to do this.
3. There are some formulae to count eligible top table rows to pair with in column K
4. There are the beginnings of a formula (array) in the range L2:S15 listing the possible first 6 (not best 6) eligible rows by their UR number.
5. Rows 17-22 are hidden - they're only my notes and can be deleted.

This is by no means a solution, rather just a quick and graphic way to test logic/algorithms; if you change the order in which you double-click rows in the top table then always double-click the asterisked option in the filtered table, it leads to very different 'best' pairings. It is just a way to help me get my head around the problem's ramifications.

BTW, in your Output sheet, UR-11 and UR-12 should not be paired since B3 is not the same as C5.

I'd like to test this with some real world data to explore the scope of possibilities. Could you oblige?

 

[ThrottleWorks]

Hi @p45cal , really sorry for such a late reply. I was down due to health issues.

I am checking your help, will revert with details. Have a nice day ahead.