I have a data in this format
Date Day Time Confirmed Home Page UV's Search Page UV's Ratio of H.S & H.P
conversion this hour UV's
1-Feb Fri 9:30 AM 424 8456 4047 0.481 0.1088
1-Feb Fri 10:30 AM 615 13689 6749 0.5163 0.0707
1-Feb Fri 11:30 AM 926 19872 10104 0.5426 0.0927
1-Feb Fri 12:30 PM 1247 27366 13842 0.4988 0.0859
1-Feb Fri 1:30 PM 1584 34334 17307 0.4973 0.0973
1-Feb Fri 2:30 PM 1895 41185 20761 0.5042 0.09

& I want to re arrange this data like below

Day Date Time Activity Value Data Type
Monday 13-Aug 10:30AM Confirmed 256 Daily
Monday 13-Aug 10:30AM Home Page NA Daily
Monday 13-Aug 10:30AM Search NA Daily
Monday 13-Aug 10:30AM Ratio 0 Daily
Monday 13-Aug 10:30AM Conversion NA Daily
Monday 13-Aug 11:30AM Confirmed 418 Daily
Monday 13-Aug 11:30AM Home Page NA Daily
Monday 13-Aug 11:30AM Search NA Daily
Monday 13-Aug 11:30AM Ratio 0 Daily
Monday 13-Aug 11:30AM Conversion NA Daily

Please help me to do this.

Assume source table as posted is in cols A to H in Sheet1, data from row2 down

In Sheet2,
In A2:E2, place these 5 formulas:
=OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/5),)
=OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/5),)
=OFFSET(Sheet1!$C$2,INT((ROWS($1:1)-1)/5),)
=INDEX({"Confirmed";"Home
Page";"Search";"Ratio";"Conversion"},MOD(ROWS($1:1 )-1,5)+1)
=OFFSET(Sheet1!$D$2,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5))
Format B1 as date, C2 as time, then copy A2:E2 down as far as required to
return desired results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dinesh" wrote:
I have a data in this format
Date Day Time Confirmed Home Page UV's Search Page UV's Ratio of H.S & H.P
conversion this hour UV's
1-Feb Fri 9:30 AM 424 8456 4047 0.481 0.1088
1-Feb Fri 10:30 AM 615 13689 6749 0.5163 0.0707
1-Feb Fri 11:30 AM 926 19872 10104 0.5426 0.0927
1-Feb Fri 12:30 PM 1247 27366 13842 0.4988 0.0859
1-Feb Fri 1:30 PM 1584 34334 17307 0.4973 0.0973
1-Feb Fri 2:30 PM 1895 41185 20761 0.5042 0.09

& I want to re arrange this data like below

Day Date Time Activity Value Data Type
Monday 13-Aug 10:30AM Confirmed 256 Daily
Monday 13-Aug 10:30AM Home Page NA Daily
Monday 13-Aug 10:30AM Search NA Daily
Monday 13-Aug 10:30AM Ratio 0 Daily
Monday 13-Aug 10:30AM Conversion NA Daily
Monday 13-Aug 11:30AM Confirmed 418 Daily
Monday 13-Aug 11:30AM Home Page NA Daily
Monday 13-Aug 11:30AM Search NA Daily
Monday 13-Aug 11:30AM Ratio 0 Daily
Monday 13-Aug 11:30AM Conversion NA Daily

Please help me to do this.

Typo, should be B2 in
Format B1 as date ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---