Hi!
I have about 55 txt files that exists as pairs. For example, File A1 is a pair of File A2, likewise with File B1 and File B2.
My goal: Compare the differences between columns & rows, and spread them out like:
Column A1 | Row A1 | Value A1 | Column A2 | Row A2 | Value A2 |
For comparison - the anchor is File _1. Hence, A1 is the anchor of A2, and B1 the anchor of B2.
This is a simple application of it. But there are some more rules:
The order is important, so the order of the columns must be standardized first, then the rows follow next.
At the end of it, I need to output to an Excel (.xslx) with multiple sheets. Each sheet corresponding to the name of the file. E.G: A, B, etc.
Thanks!
Solved! Go to Solution.
For verification:
What do you mean by "the first row is not the header"?
Also, Is there a key column for each file? Otherwise, how can you compare two tables if row order is uncertain?
Finally, is there a standard naming convention for the files that indicates which files are paired and which indicates which file is the anchor for the pairing? As of right now I am assuming that the 'A' designates the pair and '1', the anchor, but I'm not sure if you added this as a simplification to your sample data.
Hi @CoG !
Let me break it down...
So in terms of progress, I've managed to get the Column Orders sorted to match both sides, but Row Order is a complete mess. Any ideas?
If the data isn't too large, you could opt for the many-to-many Join, Formula some sort of ranking metric (equality is the simplest and what I used in the sample), and then iteratively select the best association. This is what I was thinking (I assumed that you can plug in the solution to the header issue). Very tricky problem!
Main:
Batch Macro:
Iterative Macro:
I opted for a similar approach! But your macro is much better. Thanks @CoG !
I use this method all the time. You can choose any descriptor/s in the Transpose. I've added a RecordID and used that.
If you need to find the headers, then I use a Multi-row to identify the header row and fill from there, then a filter based on that tag followed by Dynamic Rename. After that, transpose.
The first join is on all fields, with the second being on all except [Value].
This could easily be a macro, and I don't think it differs much from the CReW Expect Equal Macro.