Transpose Variable Amount of Columns in Range

Hello,

I have a range of data that is organised in rows, from which I require a certain selection of those rows, based on a cell's value, to be copied to another worksheet and rearranged in a particular fashion. Attached is an example data set illustrating the original format of the data, and the desired end result after rearranging.

Refer to the attached worksheet for references made. Essentially, each row contains identification data (columns A:C), and a variable amount of result data (columns D:X, where X can be any number of columns from D). The result data are always paired in two columns named "i.d." and "str (MPa)" respectively. The subject data set will be the rows denoted with a "yes" in the 'results entered' column (C:C). This subject data set will then be copied to a new worksheet and rearranged.

The paired columns of result data are to be transposed. So that is, the record for "BR01" (row 3) has 3 column pairs (i.e. 3 results), and when rearranged, the 3 column pairs are transposed. The outcome is the data for "BR01" is spread over 3 rows, with the identification data (columns A:C) duplicated for each of the rows where a result data was transposed. The next row from the subject data set, "BR02" (row 4) will then be copied directly below the last row from "BR01" on the new sheet and transposed the amount corresponding to the number of paired result columns, (i.e. 1), and then so on for "BR03" and "BR04". The row for "BR05" would be excluded from the copy and rearrange as it has no results entered.

Key points to be considered:
- only rows with results entered (column C:C) as "yes" are to be included
- the number of paired results will vary
- process of copying and transposing to be looped for as many rows included in subject data set (could be in the hundreds)

Thank you in advance for your input.

Cheers,
Ronjay24

Worked like a charm, thanks Richard.