dougf1r
asked on
Transpose and re-order values to a new table
I have two tables of data (attached) that I would like to transpose into the tab named "TABLE". I manually transposed the first sets of data and color coded the corresponding cells in the source and destination tabs.
The destination tab ("TABLE") does not list the values from top to bottom in the same order as the values are listed from left to right in the source tables. However, the values in Columns B and C in "TABLE" correspond with the criterion values in row 4 of the data source tables. The values in Column D of "TABLE" correspond with the data source tab, either 2050 or 2100.
I would do this manually for all cells, however, I have many spreadsheets that require the same process.
Can anyone provide an simple/automated way to do this relative to copy and paste many many times? It is ok if the destination table needs to be ordered so that the values appear top-to-bottom the same way they are listed left-to-right in the source tables.
Thanks in advance!
transpose-table.xlsx
The destination tab ("TABLE") does not list the values from top to bottom in the same order as the values are listed from left to right in the source tables. However, the values in Columns B and C in "TABLE" correspond with the criterion values in row 4 of the data source tables. The values in Column D of "TABLE" correspond with the data source tab, either 2050 or 2100.
I would do this manually for all cells, however, I have many spreadsheets that require the same process.
Can anyone provide an simple/automated way to do this relative to copy and paste many many times? It is ok if the destination table needs to be ordered so that the values appear top-to-bottom the same way they are listed left-to-right in the source tables.
Thanks in advance!
transpose-table.xlsx
aikimark
please revise your workbook, adding a new worksheet that shows the type of data transformation you need.
ASKER
I have attached a re-formatted workbook with some additional information about the data transformation.
Generally, the values in the fields labeled "med" that extend from left-to-right in the source data tables need to be transposed (top-to-bottom) in the destination table (named "TABLE"), as shown by the color coding and example crosswalk provided.
transpose-table-revised.xlsx
Generally, the values in the fields labeled "med" that extend from left-to-right in the source data tables need to be transposed (top-to-bottom) in the destination table (named "TABLE"), as shown by the color coding and example crosswalk provided.
transpose-table-revised.xlsx
look at the formulas I wrote in the destination cells
transpose-table-revised.xlsx
transpose-table-revised.xlsx
ASKER
Yes, that is the transformation I'd like to make in an automated fashion.
If you save the workbook with those formulas, you don't have to do anything else.
ASKER
I'm not sure what you mean?
Those "formulas" are just references to the source data.
How does this help me to fill in the remainder of the table without having to manually enter each cell reference individually?
Those "formulas" are just references to the source data.
How does this help me to fill in the remainder of the table without having to manually enter each cell reference individually?
I had asked you to supply me with a workbook with an additional worksheet that illustrates what you needed. You added verbiage into the existing sheet that only described the relationship of four cells. I thought that was the extent of the problem to be solved.
ASKER
What I need is illustrated on the existing worksheets.
I made the transformation manually for the first two rows (and described the relationship of four cells in more detail) as examples of how I would like to programatically populate the entire destination table using values from the source tables.
My goal is to avoid repeated copy/paste (or cell referencing) in order to fully populate the destination table.
There may be no good solution to this, but I thought it was worth a try on EE.
I made the transformation manually for the first two rows (and described the relationship of four cells in more detail) as examples of how I would like to programatically populate the entire destination table using values from the source tables.
My goal is to avoid repeated copy/paste (or cell referencing) in order to fully populate the destination table.
There may be no good solution to this, but I thought it was worth a try on EE.
In the uploaded workbook, I created two pairs of formulas and the used the autofill feature, dragging the lower right corner of the pair of cells to the right.
transpose-table-revised.xlsx
transpose-table-revised.xlsx
ASKER
This is getting close! I need to populate the whole table down to row 24. Any thoughts on how to do this?
I don't know what you're talking about, so please post another workbook after manually moving the data.
Since your blocks are only seven rows apart, I don't know what you are going to do when you are past the values and into the percentages.
Since your blocks are only seven rows apart, I don't know what you are going to do when you are past the values and into the percentages.
ASKER
I manually included cell references for filling the remainder of the destination table with values from the source table. Although I did not complete the entire destination table this way, this should reveal the pattern.
Please let me know if you need additional explanation.
transpose-table-revised-2.xlsx
Please let me know if you need additional explanation.
transpose-table-revised-2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! This works great and I am able to copy and paste to another worksheet also. Thanks very much.