Join on All Matching Fields

[KS_dup_175]

Hello! I am trying to create an automated process based on dynamic inputs. Hoping someone can help. 

Basically I need to take a data set and assign each row a 'SegmentNumber'. This number is dependent on criteria spelled out in several separate xlsx documents (one file for each SegmentNumber). These criteria files could be 1-10 fields wide, but the data set will have >100 fields. I want to be able to execute an inner joins (one for each critera doc back to the dataset) on all fields present in both files without having to manually select the fields. 

Attached is an example. If anyone has any thoughts, let me know!

[JeffF]

Hi @KS_dup_175

There might be a better way to do this, but here is a package with a couple of macros that join on all fields that are present in both input streams.

It is currently configured to only join on fields that have the same Name and Type. NOTE: In this case a String is not the same as a V_String.

You can remove the "Type" requirement by modifying the Join Tool in Macro1.yxmc.

The L, J, and, R output are the join outputs, The F output is a list of the fields that were used to join.

I hope this helps.

[terry10]

@KS_dup_175 ,

You posted this answer 5 years ago and there wasn't a single comment. But JoinMatchingFields was very helpful for me! I have ~20 files that I need to QC regularly. I use a[ transpose - Join Name/Value - Join Name] pattern to identify discrepancies between the latest version of each table and the previous version. But sometimes it's hard to track down the root cause of the discrepancy. These tables have many columns. Using your macro, I can zero in on the rows the original tables that are different. Thank you!

Your macro works for me, but I wonder if now you would stick with this approach or suggest another approach?