First in First Out Matching

[yikl]

Hi all

I have some difficulties in using Alteryx to develop a first in first out matching; and would like to seek for all of you for some advice.

Attached please see a set of sample data.  

I'd like to match (or join) on both left and right data set with title as the common key.  If I use a normal join, they all match and a total of 12 records will come out from J.  But instead I would like to build a first in first out match.  For example, the first record under Left is abc, 1 and will look up the first record in Right, abc, 6.  Since the common key title is abc, it matches and out to Join.   These 2 records will then taken out from the Left and Right dataset.  Next abc, 2 from the left matches abc, 7 on the right and they will move to Join.  This continues until end of list.  

Notice that when we move to the last record in Left (def), there is no more "def" record left in Right.  Therefore it will output to L.  Similar, the abc, 8 in Right can't find a matching pair it will go out R.

I have tried different ways of doing it but failed.  It'd be great if the community experts can provide some directions.

[jdunkerley79 · Accepted solution]

My suggestion:

Use a multi row formula after each input to make a rank by group

you can then join Title and Rank

Finally select to remove extra ranks

Sample attached

[yikl]

Thanks.  It works perfectly for my project.