Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format plan into tables
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format plan into tables
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" wrote: 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 -- AP "Little pete" a écrit dans le message de news: ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
format plan into tables
Sure!
Here is a modified version: http://cjoint.com/?gcspeUs2vb HTH -- AP "Little pete" a écrit dans le message de news: ... 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" wrote: 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 -- AP "Little pete" a écrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Format Cell | Excel Discussion (Misc queries) | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How to format a date to a different format | Excel Discussion (Misc queries) |