Transpose column of data to multiple rows of data, without having to manually 'cut' series

Hi all,

I need help in transposing a column of data to multple rows of data. Problem is the column consists of series of 5 numbers and 1 word, and I need to have a row for each series of 6. When I simply transpose the whole column, I am stuck with an enormous row that I have to manually divide into the series. It would save me a lot of time if that can be done automatically. I've solved it by filtering the IBI column and manually copy pasting every kind of IBI, beginning with IBI-2. (see sheet IBI filter) That was better than transposing but I think it can be done even faster.

The 5 numbers and the sign are labelled, so that might help. I do need the data in the same chronological it is in now. I have attached the two excel files, one where I have done all the work manually and another one that's still unaltered. I have 67 other excel files that I need to do this with.

Would love your help!

I'm sorry, I should have been clearer. They weren't actually a before and after of the same file.

I've made a file the way you said. The source is in column BW of the before sheet, I've highlighted it.

Does anyone know what to do?

Thanks, I'm waiting patiently.

I have another question you might know the answer to. How do I exclude hidden rows in a formula I use? I have filtered one of my columns on the text that is displayed, and now I want to see if there has been a 'switch' by comparing a cell with the previous cell. IF(AB2=AB5; same; switch) But when I do that, the formula automatically includes hidden rows and transforms the formule into comparing a cell with 3 cells before.

Thank you! It did work with the simplified file I sent you, but it does not work on my original files. That is partly due to some columns I have inserted since, but even after deleting those columns the macro doesn't work on my files. I also just realized that the labels 'IBI-2' are not included in the simplified files, which might make it a lot easier to transpose. The labels are in column CK in the attached file.

Could you have a look at the file I have attached and how I might change the macro to make it work?

(Ignore the sheet named 'IBI through filter', it's how I have temporarily solved this issue. By filtering on the label and copy-pasting each column next to the other.)105Frank.xlsx