SORTING MACRO TO CLEANUP MESSY SHEET

[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

 

[Mark Ivey]

I might be able to do something with this, but I need a bit more to go on...

For example, can you provide a complete listing of all sports that will be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey

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

 

[stefsailor]

hi Mark...that's fast
sorry I cannot specify the "list" of items having the SPORT or STATUS
quality any further ...
The sample sheet is only a simple pro forma version of the real chaos
i did not wanted to complicate things any further...
The actual text entries are full sentences of medical observations
in what i call the Sport items , and the "Status " data are the same type of
full text sentences again...the only charachteristic wich remains from the
original messy merged sheets regarding those entries are positional in
nature...
The "status" data remain listed in each row before the "Sports" data
troughout the entire sheet.
But like i said ...I would be already very happy if somebody could come up
with a
further version of the mentionnend " SANDY" macro only this time extracting
the "time" data instead of the "date" data and restructuring them in their
appropriate columns
This could result in a better looking result on which one could then
restructure
the text items based on their actual positional basis which seem to reflect
their original situation in the source sheets...

Furthermore ...i was terrified to see the result of my posting...is'nt there
any preview available here?
my posted sheet example looks even more chaotic then the real one...
Due to my poor editing the last column of the chaos sample sheet
has inserted itself in between the next line
maybe you guessed that?
it should be for... the chaos sheet

T>OUT
14:45
darts
22:30
18:22
18:16

and for the final expected result sheet

T>OUT
14:23
18:16
14:45
18:22
22:30

anyway thanks a lot for your answer already I did not expect such a soon reply

stef

I might be able to do something with this, but I need a bit more to go on...

For example, can you provide a complete listing of all sports that will be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey

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

 

[Mark Ivey]

If you can send me a copy of this workbook, I will look into it further. But
it sounds like there are several possibilities with the actual data. I think
I need to see it for myself to see just if I can come up with a coding
solution.

Mark Ivey (email - wmivey6311 AT hotmail DOT com)

hi Mark...that's fast
sorry I cannot specify the "list" of items having the SPORT or STATUS
quality any further ...
The sample sheet is only a simple pro forma version of the real chaos
i did not wanted to complicate things any further...
The actual text entries are full sentences of medical observations
in what i call the Sport items , and the "Status " data are the same type
of
full text sentences again...the only charachteristic wich remains from the
original messy merged sheets regarding those entries are positional in
nature...
The "status" data remain listed in each row before the "Sports" data
troughout the entire sheet.
But like i said ...I would be already very happy if somebody could come up
with a
further version of the mentionnend " SANDY" macro only this time
extracting
the "time" data instead of the "date" data and restructuring them in their
appropriate columns
This could result in a better looking result on which one could then
restructure
the text items based on their actual positional basis which seem to
reflect
their original situation in the source sheets...

Furthermore ...i was terrified to see the result of my posting...is'nt
there
any preview available here?
my posted sheet example looks even more chaotic then the real one...
Due to my poor editing the last column of the chaos sample sheet
has inserted itself in between the next line
maybe you guessed that?
it should be for... the chaos sheet

T>OUT
14:45
darts
22:30
18:22
18:16

and for the final expected result sheet

T>OUT
14:23
18:16
14:45
18:22
22:30

anyway thanks a lot for your answer already I did not expect such a soon
reply

stef

I might be able to do something with this, but I need a bit more to go
on...

For example, can you provide a complete listing of all sports that will
be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey

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

 

[David]

Hi,

It appears as if there are two lines, where there should be one line, that
the length of the data brought in was so long it put are of the data on a
second line.

It is difficult to tell if each of the two lines are in cells or if it is
one long text line? But may you just want to append every other line to the
line above it? If this is consistant for the whole length, then this could be
done with code.

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

 

[stefsailor]

hello David,
You are of course right... but that is not part of the original chaos but a
consquence of my poor editing on this site...read my answer to Mark Ivey
above...
to understand...
To resume myself ...for the moment I am loking for somebody who can add a
routine to my existing "Sandy" macro who would perform the same kind of
sorting with the time related data like the "Sandy " routine does with the
dates...
then i would have at least the names lined up under column A ( like they are
in the messy sheet to begin with) and then the dates all neatly under column
B thanks to my already existing macro named "Sandy"
and then hopefully....with the new macro "start time" under column C and
"exit time" under Column D eventually ...and then the rest of the items who
are text data but are always in the right order to one anothere in each
row...so this new macro would eventually result in a big cleanup of that mess

let my reedit the messy sheet...hope this looks better on the post
afterwarths...

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

hi Mark...that's fast
sorry I cannot specify the "list" of items having the SPORT or STATUS
quality any further ...
The sample sheet is only a simple pro forma version of the real chaos
i did not wanted to complicate things any further...
The actual text entries are full sentences of medical observations
in what i call the Sport items , and the "Status " data are the same type of
full text sentences again...the only charachteristic wich remains from the
original messy merged sheets regarding those entries are positional in
nature...
The "status" data remain listed in each row before the "Sports" data
troughout the entire sheet.
But like i said ...I would be already very happy if somebody could come up
with a
further version of the mentionnend " SANDY" macro only this time extracting
the "time" data instead of the "date" data and restructuring them in their
appropriate columns
This could result in a better looking result on which one could then
restructure
the text items based on their actual positional basis which seem to reflect
their original situation in the source sheets...

Furthermore ...i was terrified to see the result of my posting...is'nt there
any preview available here?
my posted sheet example looks even more chaotic then the real one...
Due to my poor editing the last column of the chaos sample sheet
has inserted itself in between the next line
maybe you guessed that?
it should be for... the chaos sheet

T>OUT
14:45
darts
22:30
18:22
18:16

and for the final expected result sheet

T>OUT
14:23
18:16
14:45
18:22
22:30

anyway thanks a lot for your answer already I did not expect such a soon reply

stef

I might be able to do something with this, but I need a bit more to go on...

For example, can you provide a complete listing of all sports that will be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey

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

 

[David]

Hi,

I guess it is a little too hard for me to envision,
From 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

To what: (line one)
Lydia 12/05/03 done basketball 2367 12:31 14:45 ?????
Is this correct? The order of the items is what is wrong, but all of the
data is there? You are familiar with the data, but I do not know that I
understand it. All of the data is on a single line at this point in time? It
just needs to be put in the "right" order under the correct Column. I think
it would be very difficult to do this with out actually seeing your data in a
file. All elements of data are in seperate columns already, but not in the
correct order, yes?

David

hello David,
You are of course right... but that is not part of the original chaos but a
consquence of my poor editing on this site...read my answer to Mark Ivey
above...
to understand...
To resume myself ...for the moment I am loking for somebody who can add a
routine to my existing "Sandy" macro who would perform the same kind of
sorting with the time related data like the "Sandy " routine does with the
dates...
then i would have at least the names lined up under column A ( like they are
in the messy sheet to begin with) and then the dates all neatly under column
B thanks to my already existing macro named "Sandy"
and then hopefully....with the new macro "start time" under column C and
"exit time" under Column D eventually ...and then the rest of the items who
are text data but are always in the right order to one anothere in each
row...so this new macro would eventually result in a big cleanup of that mess

let my reedit the messy sheet...hope this looks better on the post
afterwarths...

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

hi Mark...that's fast
sorry I cannot specify the "list" of items having the SPORT or STATUS
quality any further ...
The sample sheet is only a simple pro forma version of the real chaos
i did not wanted to complicate things any further...
The actual text entries are full sentences of medical observations
in what i call the Sport items , and the "Status " data are the same type of
full text sentences again...the only charachteristic wich remains from the
original messy merged sheets regarding those entries are positional in
nature...
The "status" data remain listed in each row before the "Sports" data
troughout the entire sheet.
But like i said ...I would be already very happy if somebody could come up
with a
further version of the mentionnend " SANDY" macro only this time extracting
the "time" data instead of the "date" data and restructuring them in their
appropriate columns
This could result in a better looking result on which one could then
restructure
the text items based on their actual positional basis which seem to reflect
their original situation in the source sheets...

Furthermore ...i was terrified to see the result of my posting...is'nt there
any preview available here?
my posted sheet example looks even more chaotic then the real one...
Due to my poor editing the last column of the chaos sample sheet
has inserted itself in between the next line
maybe you guessed that?
it should be for... the chaos sheet

T>OUT
14:45
darts
22:30
18:22
18:16

and for the final expected result sheet

T>OUT
14:23
18:16
14:45
18:22
22:30

anyway thanks a lot for your answer already I did not expect such a soon reply

stef

I might be able to do something with this, but I need a bit more to go on...

For example, can you provide a complete listing of all sports that will be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey



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

 

[stefsailor]

hi David
You get the picture entirely and your description of the problem is accurate
all the data are contained in the sheet, only the order inside the line has
been disturbed resulting in having a sheet with data not being under their
correct title columns anymore... like you said all elements are there "just"
the order is disturbed their's no mess between the rows and no entanglement
from one row to the other...
so for exemple..
No data from the "LYDIA row " have been messed up or inserted into a "BERT
or KEVIN etc... row" and whithin the row itself there is a consistent
ranking of the data...which is maintained troughout the entire sheet

My first attemp to solve part of the chaos has already been performed with a
macro from another forum wizzard...please read my first post...
Have a look at the script of that macro called "sandy"
it "goes and takes up" the date item along the row and inserts it beside the
"name" column
so after "Sandy" macro
that first line

Lydia done 12/05/03 2367 basketball 12:31 14:45

becomes

Lydia 12/05/03 done 2367 basketball 12:31 14:45

what i want to have in the end "ideally "
would be

Lydia 12/05/03 done basketball 2367 12:31 14:45

this line had of course an "easy" mess
it only has an inverted position for "done" and "basketball" and 2367 after
the "Sandy action"... time data are on their good positions already under
their respective titles...in this first line ...
But if you look into the other lines the result will not be so simple after
performing the Sandy macro ( put them in a sheet and see for yourself with
that macro ...)
If Somebody could deliver the "same type of macro " and append it to the
first to "collect" the time data in each line , keep them in the same order
inside the line and put them in that order into two columns beside each
other at the end of the rows like in the first Lydia row...that would be
fantastic...
I think most of my problems would be solved ...

the chaos has some other consistency...
all time data keep their order within the same lines
first comes the starting time T<IN" then next time item in the disturbed
line always is a later time ...the Finishing time ( T>out) so if a macro
rounds up the time data in each row and puts them in their same order in
adjacent columns like the Sandy macro does with the dates then I am saved for
99%...


To restructure also the text items based on content is probably impossible
because of the random unstructured nature of the content itself
There are no typical common words or string lenghts to be focused on for
filtering or manipulation purposes , they are just plain language sentences
put in there, by several different persons ( in different languages) with
their own way of expressing things...
they are small medical records, description of subjective symptoms which
should be under the title "status "and resulting therapeutic measures
taken, which should be under what I call "sports" to have a proforma
uncomplicated model of the real thing... ( and to keep the confidentiality of
the content)
The only common feature troughout the entire messy sheet, within each line
is the ranking of the text data as far as I can see
The text data expressing "Status" always preceeds the text data expressing
the "Sports "data within the same row, so probably a third macro rearanging
those text items merely on a positional basis would be the solution ...but I
do not know if it can be done with that same logic like the Sandy macro ...

there is also numeric field who refers to a file number ...but that is not
to much of a concern to me
what i really need is
1/a column with all names ( which exist already in the messy sheet)
adjacent to that column for the dates
2/which I can perform with the Sandy macro....
3/and finally columns for time in, time out ...with their time data in the
right place
if possible at he end of the row ...
and that would solve 99% of my problem ...
if of course txt roundup and numbers roundup is just a question of changing
a small "specification part" of the macro ...even better ...
but maybe my logic is entirely wrong and this should be done with an
entirely different concept

thanks for your time and effort...
stef

Hi,

I guess it is a little too hard for me to envision,
From 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

To what: (line one)
Lydia 12/05/03 done basketball 2367 12:31 14:45 ?????
Is this correct? The order of the items is what is wrong, but all of the
data is there? You are familiar with the data, but I do not know that I
understand it. All of the data is on a single line at this point in time? It
just needs to be put in the "right" order under the correct Column. I think
it would be very difficult to do this with out actually seeing your data in a
file. All elements of data are in seperate columns already, but not in the
correct order, yes?

David

hello David,
You are of course right... but that is not part of the original chaos but a
consquence of my poor editing on this site...read my answer to Mark Ivey
above...
to understand...
To resume myself ...for the moment I am loking for somebody who can add a
routine to my existing "Sandy" macro who would perform the same kind of
sorting with the time related data like the "Sandy " routine does with the
dates...
then i would have at least the names lined up under column A ( like they are
in the messy sheet to begin with) and then the dates all neatly under column
B thanks to my already existing macro named "Sandy"
and then hopefully....with the new macro "start time" under column C and
"exit time" under Column D eventually ...and then the rest of the items who
are text data but are always in the right order to one anothere in each
row...so this new macro would eventually result in a big cleanup of that mess

let my reedit the messy sheet...hope this looks better on the post
afterwarths...

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

hi Mark...that's fast
sorry I cannot specify the "list" of items having the SPORT or STATUS
quality any further ...
The sample sheet is only a simple pro forma version of the real chaos
i did not wanted to complicate things any further...
The actual text entries are full sentences of medical observations
in what i call the Sport items , and the "Status " data are the same type of
full text sentences again...the only charachteristic wich remains from the
original messy merged sheets regarding those entries are positional in
nature...
The "status" data remain listed in each row before the "Sports" data
troughout the entire sheet.
But like i said ...I would be already very happy if somebody could come up
with a
further version of the mentionnend " SANDY" macro only this time extracting
the "time" data instead of the "date" data and restructuring them in their
appropriate columns
This could result in a better looking result on which one could then
restructure
the text items based on their actual positional basis which seem to reflect
their original situation in the source sheets...

Furthermore ...i was terrified to see the result of my posting...is'nt there
any preview available here?
my posted sheet example looks even more chaotic then the real one...
Due to my poor editing the last column of the chaos sample sheet
has inserted itself in between the next line
maybe you guessed that?
it should be for... the chaos sheet

T>OUT
14:45
darts
22:30
18:22
18:16

and for the final expected result sheet

T>OUT
14:23
18:16
14:45
18:22
22:30

anyway thanks a lot for your answer already I did not expect such a soon reply

stef


:

I might be able to do something with this, but I need a bit more to go on...

For example, can you provide a complete listing of all sports that will be
in this data? With that, I think I can eliminate the SPORT item from the
STATUS item.

Mark Ivey



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

 

[David]

Hi Again,

I will try and summarize, maybe spark an idea. There are 7 elements and it
sounds like the first should be a name and I think that they are in the right
place. The second element should be a date and they are sometimes out of
order. The 3rd element should be the Status and it also is not ok. The 4th
element is a sport, not ok. 5th element is a Case #, not ok. The 6th and 7th
elements are both times and are in the proper places and order.

So:
1) Name, which is ok = Text
2) Date, not ok = Date
3) Status, not ok. How many "types" of statuses are there? Is this list
limited? = Text
4) Sport, not ok. Again, how many 'types" of sports are there? = Text
5) Case #, not ok = Number
6) Time In, ok = Time
7) Time Out, ok = Time

Maybe walk down the list, a column or two to the right of it and test the
elements, it is the two Text elements that would be hardest to test, assuming
the Name is ok, if name is not ok, then 3 elements to test. The names are not
unique like the Statuses and Sports. The case I hope is in number format, not
too hard to test. I think the times are already ok.

Does this sum it up? To do a macro we would still need to define the
elements Statuses and Sports.

Thanks,
David

 

[David]

Hi,

One more time. This will write over to Columns I through O and leave your
original data intact. Hope it helps.
It only takes into account the value you originally showed us:
Sub Macro1()
'Writes from Columns A through G to Columns I through O
Range("I1").Value = "NAME"
Range("J1").Value = "DATE"
Range("K1").Value = "STATUS"
Range("L1").Value = "SPORT"
Range("M1").Value = "CASE#"
Range("N1").Value = "T<IN"
Range("O1").Value = "T>OUT"
Range("H2").Select
Do Until ActiveCell.Offset(0, -7).Value = ""
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, -7).Value
'Test Col B -6
'Date
If ActiveCell.Offset(0, -6).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -6).Value
End If
'Status
If ActiveCell.Offset(0, -6).Value Like "done" Or _
ActiveCell.Offset(0, -6).Value Like "ignore" Or _
ActiveCell.Offset(0, -6).Value Like "open" Or _
ActiveCell.Offset(0, -6).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -6).Value
End If
'Sport
If ActiveCell.Offset(0, -6).Value Like "basketball" Or _
ActiveCell.Offset(0, -6).Value Like "soccer" Or _
ActiveCell.Offset(0, -6).Value Like "darts" Or _
ActiveCell.Offset(0, -6).Value Like "cycling" Or _
ActiveCell.Offset(0, -6).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -6).Value
End If
'Case
If ActiveCell.Offset(0, -6).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -6).Value
End If
'Time
If ActiveCell.Offset(0, -6).Value < 1 Then
IsTime1 = ActiveCell.Offset(0, -6).Value
End If
'Test Col C -5
If ActiveCell.Offset(0, -5).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -5).Value
End If
'Status
If ActiveCell.Offset(0, -5).Value Like "done" Or _
ActiveCell.Offset(0, -5).Value Like "ignore" Or _
ActiveCell.Offset(0, -5).Value Like "open" Or _
ActiveCell.Offset(0, -5).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -5).Value
End If
'Sport
If ActiveCell.Offset(0, -5).Value Like "basketball" Or _
ActiveCell.Offset(0, -5).Value Like "soccer" Or _
ActiveCell.Offset(0, -5).Value Like "darts" Or _
ActiveCell.Offset(0, -5).Value Like "cycling" Or _
ActiveCell.Offset(0, -5).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -5).Value
End If
'Case
If ActiveCell.Offset(0, -5).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -5).Value
End If
'Time
If ActiveCell.Offset(0, -5).Value < 1 Then
IsTime2 = ActiveCell.Offset(0, -5).Value
End If
'Test Col D -4
If ActiveCell.Offset(0, -4).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -4).Value
End If
'Status
If ActiveCell.Offset(0, -4).Value Like "done" Or _
ActiveCell.Offset(0, -4).Value Like "ignore" Or _
ActiveCell.Offset(0, -4).Value Like "open" Or _
ActiveCell.Offset(0, -4).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -4).Value
End If
'Sport
If ActiveCell.Offset(0, -4).Value Like "basketball" Or _
ActiveCell.Offset(0, -4).Value Like "soccer" Or _
ActiveCell.Offset(0, -4).Value Like "darts" Or _
ActiveCell.Offset(0, -4).Value Like "cycling" Or _
ActiveCell.Offset(0, -4).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -4).Value
End If
'Case
If ActiveCell.Offset(0, -4).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -4).Value
End If
'Time
If ActiveCell.Offset(0, -4).Value < 1 Then
IsTime3 = ActiveCell.Offset(0, -4).Value
End If
'Test Col E -3
If ActiveCell.Offset(0, -3).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -3).Value
End If
'Status
If ActiveCell.Offset(0, -3).Value Like "done" Or _
ActiveCell.Offset(0, -3).Value Like "ignore" Or _
ActiveCell.Offset(0, -3).Value Like "open" Or _
ActiveCell.Offset(0, -3).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -3).Value
End If
'Sport
If ActiveCell.Offset(0, -3).Value Like "basketball" Or _
ActiveCell.Offset(0, -3).Value Like "soccer" Or _
ActiveCell.Offset(0, -3).Value Like "darts" Or _
ActiveCell.Offset(0, -3).Value Like "cycling" Or _
ActiveCell.Offset(0, -3).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -3).Value
End If
'Case
If ActiveCell.Offset(0, -3).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -3).Value
End If
'Time
If ActiveCell.Offset(0, -3).Value < 1 Then
IsTime4 = ActiveCell.Offset(0, -3).Value
End If
'Test Col F -2
If ActiveCell.Offset(0, -2).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -2).Value
End If
'Status
If ActiveCell.Offset(0, -2).Value Like "done" Or _
ActiveCell.Offset(0, -2).Value Like "ignore" Or _
ActiveCell.Offset(0, -2).Value Like "open" Or _
ActiveCell.Offset(0, -2).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -2).Value
End If
'Sport
If ActiveCell.Offset(0, -2).Value Like "basketball" Or _
ActiveCell.Offset(0, -2).Value Like "soccer" Or _
ActiveCell.Offset(0, -2).Value Like "darts" Or _
ActiveCell.Offset(0, -2).Value Like "cycling" Or _
ActiveCell.Offset(0, -2).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -2).Value
End If
'Case
If ActiveCell.Offset(0, -2).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -2).Value
End If
'Time
If ActiveCell.Offset(0, -2).Value < 1 Then
IsTime5 = ActiveCell.Offset(0, -2).Value
End If
'Test Col G -1
If ActiveCell.Offset(0, -1).Value Like "*/*/*" Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, -1).Value
End If
'Status
If ActiveCell.Offset(0, -1).Value Like "done" _
Or ActiveCell.Offset(0, -1).Value Like "ignore" Or _
ActiveCell.Offset(0, -1).Value Like "open" Or _
ActiveCell.Offset(0, -1).Value Like "pending" _
Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, -1).Value
End If
'Sport
If ActiveCell.Offset(0, -1).Value Like "basketball" Or _
ActiveCell.Offset(0, -1).Value Like "soccer" Or _
ActiveCell.Offset(0, -1).Value Like "darts" Or _
ActiveCell.Offset(0, -1).Value Like "cycling" Or _
ActiveCell.Offset(0, -1).Value Like "none" _
Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, -1).Value
End If
'Case
If ActiveCell.Offset(0, -1).Value Like "###*" Then
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, -1).Value
End If
'Time
If ActiveCell.Offset(0, -1).Value < 1 Then
IsTime6 = ActiveCell.Offset(0, -1).Value
End If
'Find the times
If IsTime6 > 0 Then SaveTime2 = IsTime6
If IsTime5 > 0 Then
If SaveTime2 > 0 Then
SaveTime1 = IsTime5
Else
SaveTime2 = IsTime5
End If
Else
End If
If IsTime4 > 0 Then
If SaveTime2 > 0 Then
SaveTime1 = IsTime4
Else
SaveTime2 = IsTime4
End If
Else
End If
If IsTime3 > 0 Then
If SaveTime2 > 0 Then
SaveTime1 = IsTime3
Else
SaveTime2 = IsTime3
End If
Else
End If
If IsTime2 > 0 Then
If SaveTime2 > 0 Then
SaveTime1 = IsTime2
Else
SaveTime2 = IsTime2
End If
Else
End If
If IsTime1 > 0 Then
If SaveTime2 > 0 Then
SaveTime1 = IsTime1
Else
Stop
SaveTime2 = IsTime2
End If
Else
End If
ActiveCell.Offset(0, 7).Value = SaveTime2
ActiveCell.Offset(0, 7).NumberFormat = "h:mm"
ActiveCell.Offset(0, 6).Value = SaveTime1
ActiveCell.Offset(0, 6).NumberFormat = "h:mm"
IsTime1 = 0
IsTime2 = 0
IsTime3 = 0
IsTime4 = 0
IsTime5 = 0
IsTime6 = 0
SaveTime2 = 0
SaveTime1 = 0
ActiveCell.Offset(1, 0).Select
Loop
End Sub

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

 

[stefsailor]

hi David,
you did a great job already ...
I am one step further in the genuine re-ordering of my mess
Now I have names aligned ,dates also and time differences can be used in
further calculation in a consistent way because your routine puts the
entering and outgoing times there where they belong...
Now... my question boils down to ...
Can your "content based" columntest loop be reshaped in a "data type" test
Instead of testing for the single words ...: "none, Basketball etc..."
just on the "string"quality of its content
and then manipulate the rearanging displacement in the same order
like they occur in their row...
Like I said this STATUS and SPORT data always occur in the same order within
their row...STATUS always first SPORT afterwarths
So looking at your macro ...for the moment I skipped the
status and sport test sentences and kept the loop with date , time and case
testing
and more then 50% of my messy sheet looks alright already...
If it cannot be achieved by the "activecell commands" you use,
could one expect to ammend the cellvalue command of the "Sandy" macro into
that logic?
ie round up the string entries keep their ranking they occupy within their
row and displace them into their right columns?
or is this wishfull thinking???
thanks for all your help already
you made my week
stef