Merge, transpose and lookup values.

[macenmin]

Hello everybody,

I hope someone could help me with this, either by using a macro or a formula. I really don't think this thing I am struggling with is something very advanced but I can't get it right.

I attach an example how my data looks like so it will be easier to understand what I am trying to achieve.

The thing is that I have an Information field in column A. Column B contains a date (ex. 2015-02-25) and column C and further contains clock hour.

Example:

A B C D E F ... .... ... ...
00:00 01:00 02:00 03:00 ... .... ... ...
1 SomeText 2015-02-25 Value1 Value2 Value3 Value4
2 SomeText 2015-02-26 Value1 Value2 Value3 Value4

It can be many rows and the dates in B column is always increasing with one day per row.

What I want to achieve is the following:

A B
SomeText 2015-02-25 00:00
SomeText 2015-02-25 01:00
SomeText 2015-02-25 02:00
SomeText 2015-02-25 03:00
...
...
...

How can I do this in an easy way?
The upper part of the example file is exactly how the file that I work with looks like, not a good structure but that is how I get it.

Greatful for any help!

Thanks!!!!Merge and Transpose Example.xlsx

[Pauleyb]

A24: =INDEX($A$4:$A$19,INT((ROW(A1)-1)/24)+1+COUNTBLANK(OFFSET($A$4,0,0,INT((ROW(A1)-1)/24)+1)))
B24: =INDEX($B$4:$B$19,INT((ROW(A1)-1)/24)+1+COUNTBLANK(OFFSET($A$4,0,0,INT((ROW(A1)-1)/24)+1)))
C24: =INDEX($C$3:$Z$3,MOD(ROW(A1)-1,24)+1)
D24: =INDEX($C$4:$Z$19,INT((ROW(A1)-1)/24)+1+COUNTBLANK(OFFSET($A$4,0,0,INT((ROW(A1)-1)/24)+1)),MOD(ROW(A1)-1,24)+1)

Note, C24 can be simplified if it is always 0:00 to 23:00

[macenmin]

Brilliant!
Thanks a lot PauleyB :-) Exactly what I was looking for!!