The person who posted this question is doing something quietly ambitious: they want to turn a column of run names into a row of headers, and they want it to happen automatically every time new data lands. That is not a trivial ask. It is a structural transformation, the kind that usually requires either a pivot table, a script, or a painful afternoon of manual copying. Their instinct to avoid the manual rebuild is correct. Their instinct to feel like the workarounds have always been clunky is also correct, because most spreadsheet tools treat this as a one-off operation, not a routine workflow.
What stands out here is the honesty about the constraint. They mention that compound counts vary per run. That is the detail that breaks most naive solutions. If every run had the same number of compounds, a simple transpose or a few lookup formulas would work. But variable row counts mean the table is not a rectangle, it is a jagged shape. The user is asking for something the software should handle naturally, but does not, at least not without effort. They have tried XLOOKUP in separate tables, and they know it is brittle. That is not a failure of effort. It is a gap in the tool.
The practical takeaway is that this is exactly the kind of task where the question should not be "how do I make this work" but "what is the right mental model for this." The user is on Office 365, which means they have access to dynamic array functions like WRAPROWS, TOROW, and the newer grouping tools. These are not just conveniences. They are the building blocks for a formula-based solution that can handle variable row counts without VBA or manual intervention. The fact that the user is asking for a way to paste new data below the original and have it reorganize automatically is the right instinct. That is the definition of a sustainable workflow.
Our opinion is plain: if you are rebuilding tables by hand more than once a month, you are not doing data work, you are doing data janitorial work. The solution here is to invest a little time up front in a dynamic formula that reads the source range, identifies the run breaks, and spills the reorganized table into place. It is not magic. It is just a matter of treating the source data as a single block rather than a set of independent tables. For anyone stuck in this same loop, the first step is not to open a new sheet and start typing. It is to ask what the data looks like before it is formatted, and whether the tool you already own can be taught to reshape it for you. In this case, it can. The user just has not been shown how yet.