Normalize datasets

Use a macro, are there gaps between sets?

Upload an example and I'll write something.

This needs a little setting up so I have attached an example based on your data.

It assumes that the data is in column A and only column A ... otherwise you are going to have to do some work of your own ;-) You will need a "helper" column in column B.

The formula in cell B2 is: =IF(LEFT(A2,6)="Method",N(B1)+1,B1) ** and note that cell B1 must be blank **. You need to drag this formula down to the last row of your data.

Then, if you want to leave the rest of row 1 blank, put the following formula in cell D2 and drag across and down:

=IF(OR(ROW($A2)-1>MAX($B:$B),COLUMN(D$2)-COLUMN($D$2)>=COUNTIF($B:$B,ROW($A2)-1)),"",INDEX($A:$A,MATCH(ROW($A2)-1,$B:$B,0)+COLUMN(D$2)-COLUMN($D$2),1)&"")

You need to drag down for as many rows as you have Method statements plus 1, that is, if you have 6 Method statements, you need the formula in rows 2 to 7.

You also need to drag across as far as the maximum number of entities for a Method statement. I'm hoping you can work that out otherwise you'll need to guess.

The further you have to drag across and down, the more formulae will be generated and the slower it will be ... so, keep it to the minimum you need to transpose the data.

It is important to note that, if you put the formula in the top row, you will need to adjust it ... but see the example workbook.

Once you have the data reflected in the rows, you need to copy it and then Paste Special | Values to rremove the formulae.

You can use the following formula (in cell A1, or wherever) to count the number of Method statements:

=SUMPRODUCT((LEFT(A2:A39,6)="Method")+0)

Format as: 0 "x Method" to make it self explanatory.

Lastly, as you're in Norway, I'm guessing you'll need to change all the commas in the formulae to semi-colons.

There may be other and better ways to do this but, hopefully, it will give you something to be going on with.

Regards