This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Since both your headers and values are in the same column, and can be a mix between numbers and text, I see no way of doing this UNLESS you have a hard and fast "rule" to determine which cells in that column are headers and which are the values that go under the headers.
Is there any such rule you? In your example, it looks like the odd-numbered record IDs are the headers and the even-numbered record IDs are the values. Is that always going to be the case? It also appears that every other row is a header/value (i.e., they alternate)? Is that always the case? Will the VERY FIRST row ALWAYS be a value and then alternate from there on out as header/value, header/value?
If there is a rule, then it can be done.
Or are you doing the opposite? I can't tell from the phrasing of the question versus the examples you gave.
Here is a solution but of course I have made several assumptions.
My guess is there is likely multiple instances which perhaps have a seperate ID column to indicate which instance they belong. In my example I have created two examples. One purely with the data you have and one with how to deal with it if you have multiple instances.
What will be the case for both? That the first record will always be the value and then alternate between header and value? So you want to transform the 2-column 10 record table INTO the 1 record 5 column table?
Here's something based on the assumptions that (1) first record will always be the value, and each record there after will alternate between header and value, and (2) you want to go from 2 columns to however-many-header columns.
Step 1: Your incoming records
Step 2: Issue a record ID
Step 3: Make even records headers, and odd records values:
Assign header-value pairs the SAME number. Doesn't matter what the numbers are, as long as the alternating headers/values have the same number:
Step 5: First of a double cross-tab. Get the header/values lined up correctly:
Step 6: Second of the double cross tab: Stretch out the data into one row: