0

Sorry if the text may seem mangled but I'm writing on my phone. I have a problem that is keeping me from sleeping that I wanted input on:

Is there a way of transposing data that looks like :

ISO value1 value2 value3 value4

ISO2 value1 value2 value3 value4

To

ISO value1
ISO value2
ISO value3
ISO value4
ISO2 value1
ISO2 value2
ISO2 value3
ISO2 value4

I kept looking but I did not find a quick way of doing it.

Community
  • 1
  • 1
Doru Tudose
  • 43
  • 2
  • 10

1 Answers1

0

1) Ensure each value is in a separate cell. 2) copy both sets of values and paste-special into a new area with Transpose selected. This will give you each set in a column. If the data is initially laid out as shown in the question, the 2 sets will be in separate columns 3) In a cell beside the 1st data value, create a formula pointing to the column name. The row number in the formula should be be indicated as an absolute reference, e.g., the formula might be: "=B$1". 4) copy this formula down beside the other data values. 5) assuming the 2 sets are in separate columns, you should be able to copy the formula from step 3/4 into the cells beside each of the 2nd set of values. I.e., if you copied the formula for the ISO column into A2:A5 with the data in B2:B5, and ISO2 data is in D2:D5, copy this formula into C2:C5.

I would look for other ways if you were dealing with many sets and larger amounts of data. It also depends on how frequently you're doing this, so if the same data is coming in nightly in CSV format from an outside system, that would justify automating this transform.

Andy_in_Van
  • 331
  • 2
  • 5
  • It is a 2 per day thing, so my best bet would be automating the process. I saw something that could help relating to pivoting but I don't know. It's strange no one else had this problem previously :) – Doru Tudose Sep 18 '14 at 03:19