format plan into tables

[Guest]

I have been given heaps of maintenance plans which i need to extract the
information from. they are currently set out like the following;
With the asset on the side, days on the top and letters in the middle which
represent the maintainence task.


DAYS
1 2 3 4 5 6 7
Asset 1 D
Asset 2 F
Asset 3 D
Asset 4 D
Asset 5
Asset 6 E
Asset 7 E


I want to be able to quickly format, run a macro, or code to get this
information into the following format.

Task Day
Asset 1 D 1
Asset 4 D 2
Asset 7 E 2
Asset 2 F 3
Asset 6 E 6
Asset 3 D 7


Any help on this would be great.

Cheers Peter

 

[Ardus Petus]

Say your original table (with day# headers) is in A1:H8

Results for Asset 1 are given by following Array formula:
=INDEX(B2:H2,1,MATCH(FALSE,ISBLANK(B2:H2),0))&"
"&MATCH(FALSE,ISBLANK(B2:H2),0)
which must be validated with Ctrl+Shift+Enter.

You can copy it down for the other assets

See example: http://cjoint.com/?gcnYyVtWmH

HTH

 

[Guest]

Is there a way that the formular can pick up the Asset number at the same
time. i.e. paste a formula which captures the asset number, task, day - all
in seperate columns?

Thanks

also what does validate with Ctrl+Shift+Enter mean??

 

[Ardus Petus]

Sure!
Here is a modified version: http://cjoint.com/?gcspeUs2vb

HTH