Most challenging Excel formatting ever! Conditional Transpose with limited delimiters!

Yes, this is public information.

Perhaps the source data is an unnecessarily large distribution of public data. I think there is enough in the XLS to illustrate the problem.

Thanks. So far, still no luck...

Originally Posted by JieJenn

The problem is there are no common patterns with the data provided. It's very difficult to determine which item cut/paste to which filed as criteria is lacking.

True. The only method that seems to make sense would be a java script request that looks up the first 5 characters as a true or false based on being all text or containing a single pipe (|), if script detects the pipe then create initiate transpose for the following rows that also contain pipes (|, ||, |||), once the script detects 5 characters again that do not contain a pipe, then it knows to ignore those rows until it finds the next row with a single pipe.

Unfortunately I don't know what a javascript like this would look like. I experimented a bit with MATCH and INDEX functions but couldn't put my finger on the exact method.