Unique tool without excluding dupliactes

[DanMax]

Hey guys, 

I am matching a set of case firms with a set of control firms based on industry classification and asset size. I want to match each case firm with the closest control firm in terms of asset size (within an industry). However, I want to make sure, that each control firm is only included once. The problem is, that if I sort by Asset Difference and then apply the Unique tool to filter for unique control firms, I eliminate all the other possible combinations for the second case firm.

In the event of two case firms leading to the same control firm, I want the second case firm two go for the second closest control firm in terms of asset size. Please see the Example below. The best match for Firm ABC and Firm XYZ would be Firm E. (closet asset difference). As the same control firm can not be included twice, Firm XYZ should go for the second closest control firm, which is in this example Firm D. 

Input

Case Firm	Total Assets	Industry
Firm ABC	100	1
Firm XYZ	1000	1

Control Firms	Total Assets	Industry
Firm D	1800	1
Firm E	1100	1
Firm F	2000	1

Match

Case Firm	Control Firm	Asset Difference (absolute)	Industry
Firm ABC	Firm D	1700	1
Firm ABC	Firm E	1000	1
Firm ABC	Firm F	1900	1
Firm XYZ	Firm D	800	1
Firm XYZ	Firm E	100	1
Firm XYZ	Firm F	1000	1

Wanted Output 

Case Firm	Control Firm	Asset Difference (absolute)	Industry
Firm ABC	Firm E	1000	1
Firm XYZ	Firm D	800	1

Do you have any idea how to implement this on Alteryx? 

Thank you very much and have a nice day

[AngelosPachis · Accepted solution]

Hi @DanMax ,

I consider that to be possible in all workflows but each one of them will have a different tweak. The key here would not be to make all of them work of course, but understand more or less how they work as you might need that knowledge going forward.

In the workflow I have attached in my previous post, all you need to do is open the iterative macro and change the sort tool so you only sort on the Difference field and then save the new iterative macro.

That will match XYZ with E and in the next run ABC will match with D.

Hope that makes sense in a way, let me know if there are any questions.

Cheers,

Angelos

[mceleavey · Accepted solution]

@DanMax ,

I've attached the new macro using the difference to drive the first selection.

This gives the following:

Let me know if this is correct.

M.

[DanMax]

Thanks guys, I will look into this and let you know asap. 

[DanMax]

Thanks a lot guys, it works perfectly!