CODEWITHSUNDEEP
excelvba
Doru Tudose
Doru Tudose

Reputation: 43

Transpose data filling down first column

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.

Upvotes: 0

Views: 347

Answers (1)

Andy_in_Van
Andy_in_Van

Reputation: 331

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.

Upvotes: 0

Related Questions