S
stefsailor
I have received a "chaotic "sheet coming( presumably) from inorderly and
faulty mergers from different source sheets ( who are lost...), from a
collegue, to restructure it into an orderly one...
I work on Office with excel 2002
how in Godsname am i going to do this otherwise than restructure each line
manually ( sheet is over 3500 lines with 12 columns coming from a structured
but lost original ...siggh..!!!...I received a macro for doing already part
of the job ....(look further please...)
my chaos sheet looks lik this...
NAME DATE STATUS SPORT CASE# T<IN
T>OUT
Lydia done 12/05/03 2367 basketball 12:31
14:45
Bert 23/08/07 12:33 356899 14:23 pending
darts
Kevin ignore 24/08/05 11:56 soccer 124587
22:30
Lydia 12:30 done 56875585 none 18/04/95
18:22
Bert open 458 cycling 11:22
10/02/1999 18:16
what i need in the end is "of course...":
NAME DATE STATUS SPORT CASE# T>IN
T>OUT
Bert 23/08/07 pending darts 356899 12:33
14:23
Bert 10/02/1999 open cycling 458
11:22 18:16
Lydia 12/05/03 done basketball 2367 12:31
14:45
Lydia 18/04/95 done none 56875585 12:30
18:22
Kevin 24/08/05 ignore soccer 124587 11:56
22:30
the only remaining consistencies from the source spreadsheets in that
chaotic sheet
are:
Names always in the first collumn A
the dates are in the format as shown,
sometimes full year 4 digits i.e.: "1999"
sometimes only the two last digits for the year i.e. : "05"
the time format is always custom: h:mm
the start time data entries in the same row are always preceding the
outgoing time entries ( later i have to perform a duration calculation on
those times in an extra column)
from row to row this time entries change their positions but always start
time before end time ...
The other columns are text formats
and the text entry corresponding to the "STATUS" heading "quality" always
preceeds the text entry corresponding to the "SPORT" quality
I found no other consistencies than this
like i said earlier I have already a macro who picks up the dates from each
row and puts it in an orderly colum under DATE
I have to select the entire sheet under the titles from A2 till G6 and then
I let the macro run and it does this:
NAME DATE STATUS SPORT CASE# T>I T>OUT
Lydia 12/05/03 done 2367 basketball 12:31 14:45
Bert 23/08/07 12:33 356899 14:23 pending darts
Kevin 24/08/05 ignore 11:56 soccer 124587 22:30
Lydia 18/04/95 12:30 done 56875585 none 18:22
Bert 10/02/99 open 458 cycling 11:22 18:16
which looks a lot better already
the macro goes like this
Sub sandy()
On Error Resume Next
Dim DRange As Range, mCell As Range
For Each mCell In Selection
If IsDate(mCell) = True And Not mCell.Column = 2 Then
mCell.Cut
Cells(mCell.Row, "B").Insert (xlToRight)
End If
Next
Application.CutCopyMode = False
For Each mCell In Range("B2", Cells(Columns(2).Rows.Count, "B").End(xlUp))
mCell.Value = CDate(mCell)
Trim (mCell)
mCell.NumberFormat = "dd/mm/yy"
mCell.HorizontalAlignment = xlCenter
Next
[A1].Select
End Sub
i've called it "SANDY" in honour to the person who wrote it for me on
another forum
I cannot write any macro's ...and Sandy has dissappeared...sadly...
My question ...
Can anyone else maybe continue this routine and add the next step
maybe sort out the time entries in two further juxtaposed collumns
just like the Sandy macro does with the dates...?
maybe then the rest of it will "fall into place" much better already
thanks for keeping with me so far
and all help will be deeply appreciated
stef
faulty mergers from different source sheets ( who are lost...), from a
collegue, to restructure it into an orderly one...
I work on Office with excel 2002
how in Godsname am i going to do this otherwise than restructure each line
manually ( sheet is over 3500 lines with 12 columns coming from a structured
but lost original ...siggh..!!!...I received a macro for doing already part
of the job ....(look further please...)
my chaos sheet looks lik this...
NAME DATE STATUS SPORT CASE# T<IN
T>OUT
Lydia done 12/05/03 2367 basketball 12:31
14:45
Bert 23/08/07 12:33 356899 14:23 pending
darts
Kevin ignore 24/08/05 11:56 soccer 124587
22:30
Lydia 12:30 done 56875585 none 18/04/95
18:22
Bert open 458 cycling 11:22
10/02/1999 18:16
what i need in the end is "of course...":
NAME DATE STATUS SPORT CASE# T>IN
T>OUT
Bert 23/08/07 pending darts 356899 12:33
14:23
Bert 10/02/1999 open cycling 458
11:22 18:16
Lydia 12/05/03 done basketball 2367 12:31
14:45
Lydia 18/04/95 done none 56875585 12:30
18:22
Kevin 24/08/05 ignore soccer 124587 11:56
22:30
the only remaining consistencies from the source spreadsheets in that
chaotic sheet
are:
Names always in the first collumn A
the dates are in the format as shown,
sometimes full year 4 digits i.e.: "1999"
sometimes only the two last digits for the year i.e. : "05"
the time format is always custom: h:mm
the start time data entries in the same row are always preceding the
outgoing time entries ( later i have to perform a duration calculation on
those times in an extra column)
from row to row this time entries change their positions but always start
time before end time ...
The other columns are text formats
and the text entry corresponding to the "STATUS" heading "quality" always
preceeds the text entry corresponding to the "SPORT" quality
I found no other consistencies than this
like i said earlier I have already a macro who picks up the dates from each
row and puts it in an orderly colum under DATE
I have to select the entire sheet under the titles from A2 till G6 and then
I let the macro run and it does this:
NAME DATE STATUS SPORT CASE# T>I T>OUT
Lydia 12/05/03 done 2367 basketball 12:31 14:45
Bert 23/08/07 12:33 356899 14:23 pending darts
Kevin 24/08/05 ignore 11:56 soccer 124587 22:30
Lydia 18/04/95 12:30 done 56875585 none 18:22
Bert 10/02/99 open 458 cycling 11:22 18:16
which looks a lot better already
the macro goes like this
Sub sandy()
On Error Resume Next
Dim DRange As Range, mCell As Range
For Each mCell In Selection
If IsDate(mCell) = True And Not mCell.Column = 2 Then
mCell.Cut
Cells(mCell.Row, "B").Insert (xlToRight)
End If
Next
Application.CutCopyMode = False
For Each mCell In Range("B2", Cells(Columns(2).Rows.Count, "B").End(xlUp))
mCell.Value = CDate(mCell)
Trim (mCell)
mCell.NumberFormat = "dd/mm/yy"
mCell.HorizontalAlignment = xlCenter
Next
[A1].Select
End Sub
i've called it "SANDY" in honour to the person who wrote it for me on
another forum
I cannot write any macro's ...and Sandy has dissappeared...sadly...
My question ...
Can anyone else maybe continue this routine and add the next step
maybe sort out the time entries in two further juxtaposed collumns
just like the Sandy macro does with the dates...?
maybe then the rest of it will "fall into place" much better already
thanks for keeping with me so far
and all help will be deeply appreciated
stef