|
SORTING MACRO TO CLEANUP MESSY SHEET
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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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) "stefsailor" wrote in message ... 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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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. "stefsailor" wrote: 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 TOUT 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 "stefsailor" wrote: 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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
Hi,
I guess it is a little too hard for me to envision, From this: NAME DATE STATUS SPORT CASE# T<IN TOUT 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 "stefsailor" wrote: 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 TOUT 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 "stefsailor" wrote: 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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 ( Tout) 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 "David" wrote: Hi, I guess it is a little too hard for me to envision, From this: NAME DATE STATUS SPORT CASE# T<IN TOUT 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 "stefsailor" wrote: 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 TOUT 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 "stefsailor" wrote: 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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 "stefsailor" wrote: 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 ( Tout) 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 "David" wrote: Hi, I guess it is a little too hard for me to envision, From this: NAME DATE STATUS SPORT CASE# T<IN TOUT 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 "stefsailor" wrote: 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 TOUT 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 "stefsailor" wrote: 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 TOUT 14:45 darts 22:30 18:22 18:16 and for the final expected result sheet TOUT 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 "Mark Ivey" wrote: 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 "stefsailor" wrote in message ... 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 = "TOUT" 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 "stefsailor" wrote: 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 TOUT 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# TIN TOUT 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 a 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# TI TOUT 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 |
SORTING MACRO TO CLEANUP MESSY SHEET
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 "David" wrote: 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 = "TOUT" 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 "stefsailor" wrote: 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 TOUT 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# TIN TOUT 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 a Names always in the first collumn A the dates are in the format as shown, |
| All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com