VB Code or formula to rearrange data

Hi Riz,

Happy New Year to you.

You can put this formula in cell D4 of Sheet2:

=IFERROR(IF(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0)="","",IF(ISNUMBER(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0)),TEXT(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0),"h:mm")&" to "&TEXT(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:E),0),"h:mm"),VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0))),"")

then copy it down into D5:D60. Then copy the block of cells from D4:D60 across into the other columns, at F4, H4, J4 and so on (i.e. the other yellow columns).

Hope this helps.

Pete

Hello Pete_UK:

Seems to work with small glitch.
Please refer to attached file.
2nd part of the data for the day is not correct.
Example :

Sheet2 : Cell D6 shows 15:00 to 15:00 and this needs to be 15:00 to 17:00


Let me know if you have any questions.
Thanks.

Riz

Hello Riz,

this is the formula that you have in D4:

=IFERROR(IF(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0)="","",IF(ISNUMBER(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0)),TEXT(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0),"h:mm")&" to "&TEXT(VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0),"h:mm"),VLOOKUP($C4,Sheet1!$C:$Q,COLUMNS($C:D),0))),"")

Note that the D which is shown in red should be E.

I've attached the file that I worked on this morning to show this.

Hope this helps.

Pete

Hello Pete and Winon:

Solution works great, totally forgot.
Winon, you knew what i was getting at.
Thanks a lot
Riz