Identify which files (out of multiple) have same column headers

Question

Hi, I have a consolidated dummy data set of multiple excel files-In actuality around 700 excels consolidated in the real data. I have a column called filename which varies for each excel. I used a batch macro to consolidate all this data-so all the column headers have come in this dataset. Not all column headers are applicable for all files-I'm looking to find out which excels-aka filesnames are same in terms of column headers i.e. data in those columns isnt null and which are different i.e. the column info is null in them vs other excels (filenames) without null column names for the same column header. I tried filtering on filenames(which obviously won't work considering 700 excels)-multi-join also isnt working considering I want to know which filenames don't match on the column headers-A macro solution is what would be required here. Nevertheless, i'm attaching the dummy data as a starting point here. Ultimately I want a summary list of how many filenames have columns matching/how many are different.

Profiles match or not.yxzp

ChrisTX · Answer

Yes, if you have a list of unique field names across all of your files, you can use a macro to update the XML of the Summarize tool.

Here are a few posts about dynamically updating a tool's XML:

Dynamic Summarize - Group By ALL text fields and Sum ALL numeric fields
  Update XML using a Batch Macro
  Update Inner XML
  Update Raw XML with Formula

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Unknown-Select-Feature-w-Summarize-Tool/td-p/327371

Dynamic Transpose Key Field + Summary Field
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Transpose-Key-Field-Summary-Field/td-p/15755/highlight/true

List box with Transpose

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/List-box-with-Transpose/td-p/514539

Join Dynamically - allow user to specify Parameter values for fields to Join on - Update inner XML
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/joining-tables-dynamically/td-p/163022

Visual example of the difference between an inner and outer XML update:
https://community.alteryx.com/t5/Data-Preparation-Blending/Dynamic-Transpose-Key-Field-Summary-Field/m-p/16212/highlight/true#M2929

Unable to pass Selection fields data to Join tool using 2 Listbox tools
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Unable-to-pass-Selection-fields-data-to-Join-tool-using-2/td-p/469123

Chris

Shaina1 · Answer

Thanks @ChrisTX -this was a good starting point indeed-I was able to tweak it a bit to get to where I want.Just wanted to ask that considering that I have 700 excels and around 220 headers-it would be very tedious to add in all fields for count non nulls in the summarize tool-was wondering whether there is any dynamic way to do this using batch macros. I really wanted to post my solution-but it seems my company controls are preventing me from posting anymore :(

ChrisTX · Answer

Try this as a starting point.  If you need the highlighted Summarize tool to be dynamic (recognize new fields), you could write a macro to update that tool's XML.

Chris

Profiles match or not.yxmd