Transposing irregular data separated until date is found

[Metatron112]

Hi all,

I've been searching a lot, but could not find a solution for the specific problem I have.

I have a long list of data, in which a date is followed by an irregular amount of text strings in the following form:

Date1
Text1
Text2
Text3
Date2
Text4
Date3
Text5
Text6
....

I would like to transpose this data, so that it is displayed in rows always beginning with a date and the following text elements in columns until the next date is found:

Date1 Text1 Text2 Text3
Date2 Text4
Date3 Text5 Text6

I hope you can point me in the right direction on how to do this.

Thanks in advance

[BellyGas]

Do the text entries ever start with a number?

If not, you could just scan down the column with an If left$(range("whatever").value, 1) > 0 then.... and then it's just a case of moving cell entries around and removing the empty rows.

[Metatron112]

The date is always in a form like "12.01." and the text strings never have a number in them.

Could you elaborate a bit on your idea, this sounds like it might work.

[MickG]

This will work if your "Dates" are true dates (not text)
NB:- Results start "C1"

Please Login or Register  to view this content.

Regards Mick

[Metatron112]

Thanks Mick for the quick help!!! This works like a charm.

Tobias

[MickG]

You're welcome

[daboho]

Please Login or Register  to view this content.