I was given data in the following format:

00 10 20 30 40 50
0 25 30 41 43 36 38
1 40 56 52 38 35 20
2 25 19 38 44 46 50
3 52 58 48 56 50 48

Is there an easy way to convert it to the following format:

0:00 25
0:10 30
0:20 41
0:30 43
0:40 36
0:50 38
1:00 40
1:10 56, etc.

Any suggestions would be apprecated.

Assume data is in G1 to M25
go to an open area C1?
insert "0" and copy down to C6
in C7 enter
=C1+1
copy down til you have 6 of the highest number you need.
For my example to C146
copy the cells containing 00 to 50 (H1:M1)
select D1 and paste special transpose
copy D1:D6 and paste to end of data in Column C (D144)
in E1 enter

=C1&":"&D1
copy down to E144
in F1 enter
=vlookup(C1,$H$1:$M$25,D1/10+2,False)
and copy down to F144


"Eaglered" wrote:

I was given data in the following format:

00 10 20 30 40 50
0 25 30 41 43 36 38
1 40 56 52 38 35 20
2 25 19 38 44 46 50
3 52 58 48 56 50 48

Is there an easy way to convert it to the following format:

0:00 25
0:10 30
0:20 41
0:30 43
0:40 36
0:50 38
1:00 40
1:10 56, etc.

Any suggestions would be apprecated.

Thank you, bj for the information. We got your solution to work on a small
scale but now we are trying to get it to work with a month's worth of ten
minute data and we are running into some issues. Can you explain your
reasoning behind the lookup_value of C1 and the Col_Index_num of D1/10+2?

Thank you for your help!!

"bj" wrote:

Assume data is in G1 to M25
go to an open area C1?
insert "0" and copy down to C6
in C7 enter
=C1+1
copy down til you have 6 of the highest number you need.
For my example to C146
copy the cells containing 00 to 50 (H1:M1)
select D1 and paste special transpose
copy D1:D6 and paste to end of data in Column C (D144)
in E1 enter

=C1&":"&D1
copy down to E144
in F1 enter
=vlookup(C1,$H$1:$M$25,D1/10+2,False)
and copy down to F144


"Eaglered" wrote:

I was given data in the following format:

00 10 20 30 40 50
0 25 30 41 43 36 38
1 40 56 52 38 35 20
2 25 19 38 44 46 50
3 52 58 48 56 50 48

Is there an easy way to convert it to the following format:

0:00 25
0:10 30
0:20 41
0:30 43
0:40 36
0:50 38
1:00 40
1:10 56, etc.

Any suggestions would be apprecated.