DATA SORTING MACRO

[stefsailor]

I have a totally messed up spreadsheet coming from multiple mergers of
spreadsheets with basically the same data but different column title
arrangements
My sheet looks like this

A B C D
E F
1 NAME DATE QUEST? ANSWER! =SOLUTION <COMMENT>
2 Jan 22/06/05 why? Black! = 3586
< 0>
3 Karen 4/10/04 yellow! =ok why not ?
<->
4 Johann red! maybe? <ASAP> =pending
31/08/06
5 Stefan green! when? <Now> 10/10/05
=325
6 John who? Nobody! 20/10/06 =in prgrss
<impossible>
7 Karen yellow! =415 why not? <impossible>
3/10/04
8 Cecil =none 01/02/06 grey! =donE
<positive>
9 Johann 18/02/04 blue! where? =Positive < n
acceptable>
etc….

I want to restore a “normal sheet†with the right data in the right columns
…without loosing the horizontal integrity however.
Currently I do this manually with multiple auto filter and then copy past
the data in a new spreadsheet to construct a little excerpt for the business
on hand with a particular person when and if needed. Then I obtain …tediously

A B C D E
F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? Red! =Pending <ASAP>
3 Johann 18/02/06 where? Blue! =positiv <not
acceptable>

It would be handy to do this one’s and forever for the entire spreadsheet
with a vba routine if at any possible given the chaos!!!
The only consistency throughout the whole spreadsheet is that
names are kept under NAMES and all the items summed up in the row after
that particular name( say in line 6 for John…) has been performed at that
particular date (who appears in E and should be in C6) for that person
….so... should be kept together in the new spreadsheet
If , of course, somebody could write a routine to clean up he entire mess
that would be fantastic; but I would already be happy if I could sort out
automatically a name and date consistently with the rest of the horizontal
rows like this:
A B C D
E F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? <ASAP> =pending red!
3 Johann 18/02/04 blue! where? =Positive
< not acceptable

The problem boils down to a date filtering, extracting loop routine in my
opinion ....
To make it even more difficult : the signs: ? < > = ! are just used here to
differentiate the nature of the data since this is a hypothetical table …in
reality these data are large text strings, or when it comes to the heading
“=SOLUTION†sometimes a number format (this can give some surprises when
trying to extract dates in their number format)so this signs cannot be used
in a filtering vba routine as criteria to handle the restructuring of the
chaos
I hope somebody can help …?
Thousand thanks in advance…

 

[Sandy]

Hey stefsailor here, at least for now, is a way to move your dates to
column "B" and format them to "dd/mm/yy". I'm not too sure how to move
the rest due to them being text strings. In each column is there a
finite number of different strings? for example: your question column =
maybe, when, who, etc. are there 10 questions or could it be
anything... if it is finite, for each column you could make a
collection of strings to search through to determine what column the
string should be in.

let me know

so you sail huh? out of where? I do a lot of racing on the east coast
of the US and Carribean...

HTH
Sandy

(code below)

Sub test()
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

 

[stefsailor]

i'll come back when i've done my housework with your code ...yes I am sailing
when I do not work... Belgian Dutch and French Atlantic in mid autumn Greek
Islands and Turkey not racing ...cruising.
thank you very much for now I was affraid the task was so complex nobody
would reply and I am fairly new to VBA ...be back soon

Hey stefsailor here, at least for now, is a way to move your dates to
column "B" and format them to "dd/mm/yy". I'm not too sure how to move
the rest due to them being text strings. In each column is there a
finite number of different strings? for example: your question column =
maybe, when, who, etc. are there 10 questions or could it be
anything... if it is finite, for each column you could make a
collection of strings to search through to determine what column the
string should be in.

let me know

so you sail huh? out of where? I do a lot of racing on the east coast
of the US and Carribean...

HTH
Sandy

(code below)

Sub test()
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 have a totally messed up spreadsheet coming from multiple mergers of
spreadsheets with basically the same data but different column title
arrangements
My sheet looks like this

A B C D
E F
1 NAME DATE QUEST? ANSWER! =SOLUTION <COMMENT>
2 Jan 22/06/05 why? Black! = 3586
< 0>
3 Karen 4/10/04 yellow! =ok why not ?
<->
4 Johann red! maybe? <ASAP> =pending
31/08/06
5 Stefan green! when? <Now> 10/10/05
=325
6 John who? Nobody! 20/10/06 =in prgrss
<impossible>
7 Karen yellow! =415 why not? <impossible>
3/10/04
8 Cecil =none 01/02/06 grey! =donE
<positive>
9 Johann 18/02/04 blue! where? =Positive < n
acceptable>
etc....

I want to restore a "normal sheet" with the right data in the right columns
...without loosing the horizontal integrity however.
Currently I do this manually with multiple auto filter and then copy past
the data in a new spreadsheet to construct a little excerpt for the business
on hand with a particular person when and if needed. Then I obtain ...tediously

A B C D E
F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? Red! =Pending <ASAP>
3 Johann 18/02/06 where? Blue! =positiv <not
acceptable>

It would be handy to do this one's and forever for the entire spreadsheet
with a vba routine if at any possible given the chaos!!!
The only consistency throughout the whole spreadsheet is that
names are kept under NAMES and all the items summed up in the row after
that particular name( say in line 6 for John...) has been performed at that
particular date (who appears in E and should be in C6) for that person
....so... should be kept together in the new spreadsheet
If , of course, somebody could write a routine to clean up he entire mess
that would be fantastic; but I would already be happy if I could sort out
automatically a name and date consistently with the rest of the horizontal
rows like this:
A B C D
E F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? <ASAP> =pending red!
3 Johann 18/02/04 blue! where? =Positive
< not acceptable

The problem boils down to a date filtering, extracting loop routine in my
opinion ....
To make it even more difficult : the signs: ? < > = ! are just used here to
differentiate the nature of the data since this is a hypothetical table ...in
reality these data are large text strings, or when it comes to the heading
"=SOLUTION" sometimes a number format (this can give some surprises when
trying to extract dates in their number format)so this signs cannot be used
in a filtering vba routine as criteria to handle the restructuring of the
chaos
I hope somebody can help ...?
Thousand thanks in advance...

 

[stefsailor]

Awesome Sandy...!!!
You wrote a magnificent piece of VBA!!!
My first frustration is already taken care of
Your macro works wonderful
The horizontal data are kept together...the names and the dates are aligned
in A and B,
and the result of your macro (incredible ...such a short piece of code ...)
shows that there is some consistency in the messed up chaos after all...
I did a test on the sample sheet which I posted and then on the real thing
....the result was astonishing...it takes about 4,5 minutes to sort the dates
to column B for 6789 entree lines over 20 columns (right up to column T) this
would have taken me more or less 4,5 days
or more probably with filter copy past to another sheet …
The fact that you succeeded in sorting an reassembling the dates in B shows
more logic and consistency in the rest of the chaos, apparently the merger of
al this spreadsheets has not entirely disrupted the entire pattern of the
entrees.
The next thing to sort might be data in time formats…start time of the
questioning and end time of the questioning randomly dispersed in the sheet
like the dates you managed to sort so accurately…
I am going to analyse this result a little bit more because I think
that I could give you more clues to give answers to your questions in order
to extend your macro to a full sorting masterpiece...I’ll be back soon with a
new sample sheet of how it looks now after your macro …
Even if I am maybe too enthusiastic right now about the feasibility of the
entire clean-up … ...already a million thanks for what you accomplished
stef

 

[stefsailor]

Awesome Sandy...!!!
You wrote a magnificent piece of VBA!!!
My first frustration is already taken care of
Your macro works wonderful
The horizontal data are kept together...the names and the dates are aligned
in A and B,
and the result of your macro (incredible ...such a short piece of code ...)
shows that there is some consistency in the messed up chaos after all...
I did a test on the sample sheet which I posted and then on the real thing
....the result was astonishing...it takes about 4,5 minutes to sort the dates
to column B for 6789 entree lines over 20 columns (right up to column T) this
would have taken me more or less 4,5 days
or more probably with filter copy past to another sheet …
The fact that you succeeded in sorting an reassembling the dates in B shows
more logic and consistency in the rest of the chaos, apparently the merger of
al this spreadsheets has not entirely disrupted the entire pattern of the
entrees.
The next thing to sort might be data in time formats…start time of the
questioning and end time of the questioning randomly dispersed in the sheet
like the dates you managed to sort so accurately…
I am going to analyse this result a little bit more because I think
that I could give you more clues to give answers to your questions in order
to extend your macro to a full sorting masterpiece...I’ll be back soon with a
new sample sheet of how it looks now after your macro …
Even if I am maybe too enthusiastic right now about the feasibility of the
entire clean-up … ...already a million thanks for what you accomplished
stef

 

[stefsailor]

Awesome Sandy...!!!
You wrote a magnificent piece of VBA!!!
My first frustration is already taken care of
Your macro works wonderful
The horizontal data are kept together...the names and the dates are aligned
in A and B,
and the result of your macro (incredible ...such a short piece of code ...)
shows that there is some consistency in the messed up chaos after all...
I did a test on the sample sheet which I posted and then on the real thing
....the result was astonishing...it takes about 4,5 minutes to sort the dates
to column B for 6789 entree lines over 20 columns (right up to column T) this
would have taken me more or less 4,5 days
or more probably with filter copy past to another sheet …
The fact that you succeeded in sorting an reassembling the dates in B shows
more logic and consistency in the rest of the chaos, apparently the merger of
al this spreadsheets has not entirely disrupted the entire pattern of the
entrees.
The next thing to sort might be data in time formats…start time of the
questioning and end time of the questioning randomly dispersed in the sheet
like the dates you managed to sort so accurately…
I am going to analyse this result a little bit more because I think
that I could give you more clues to give answers to your questions in order
to extend your macro to a full sorting masterpiece...I’ll be back soon with a
new sample sheet of how it looks now after your macro …
Even if I am maybe too enthusiastic right now about the feasibility of the
entire clean-up … ...already a million thanks for what you accomplished
stef

 

[stefsailor]

Awesome Sandy...!!!
You wrote a magnificent piece of VBA!!!
My first frustration is already taken care of
Your macro works wonderful
The horizontal data are kept together...the names and the dates are aligned
in A and B,
and the result of your macro (incredible ...such a short piece of code ...)
shows that there is some consistency in the messed up chaos after all...
I did a test on the sample sheet which I posted and then on the real thing
....the result was astonishing...it takes about 4,5 minutes to sort the dates
to column B for 6789 entree lines over 20 columns (right up to column T) this
would have taken me more or less 4,5 days
or more probably with filter copy past to another sheet …
The fact that you succeeded in sorting an reassembling the dates in B shows
more logic and consistency in the rest of the chaos, apparently the merger of
al this spreadsheets has not entirely disrupted the entire pattern of the
entrees.
The next thing to sort might be data in time formats…start time of the
questioning and end time of the questioning randomly dispersed in the sheet
like the dates you managed to sort so accurately…
I am going to analyse this result a little bit more because I think
that I could give you more clues to give answers to your questions in order
to extend your macro to a full sorting masterpiece...I’ll be back soon with a
new sample sheet of how it looks now after your macro …
Even if I am maybe too enthusiastic right now about the feasibility of the
entire clean-up … ...already a million thanks for what you accomplished
stef

i'll come back when i've done my housework with your code ...yes I am sailing
when I do not work... Belgian Dutch and French Atlantic in mid autumn Greek
Islands and Turkey not racing ...cruising.
thank you very much for now I was affraid the task was so complex nobody
would reply and I am fairly new to VBA ...be back soon

Hey stefsailor here, at least for now, is a way to move your dates to
column "B" and format them to "dd/mm/yy". I'm not too sure how to move
the rest due to them being text strings. In each column is there a
finite number of different strings? for example: your question column =
maybe, when, who, etc. are there 10 questions or could it be
anything... if it is finite, for each column you could make a
collection of strings to search through to determine what column the
string should be in.

let me know

so you sail huh? out of where? I do a lot of racing on the east coast
of the US and Carribean...

HTH
Sandy

(code below)

Sub test()
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 have a totally messed up spreadsheet coming from multiple mergers of
spreadsheets with basically the same data but different column title
arrangements
My sheet looks like this

A B C D
E F
1 NAME DATE QUEST? ANSWER! =SOLUTION <COMMENT>
2 Jan 22/06/05 why? Black! = 3586
< 0>
3 Karen 4/10/04 yellow! =ok why not ?
<->
4 Johann red! maybe? <ASAP> =pending
31/08/06
5 Stefan green! when? <Now> 10/10/05
=325
6 John who? Nobody! 20/10/06 =in prgrss
<impossible>
7 Karen yellow! =415 why not? <impossible>
3/10/04
8 Cecil =none 01/02/06 grey! =donE
<positive>
9 Johann 18/02/04 blue! where? =Positive < n
acceptable>
etc....

I want to restore a "normal sheet" with the right data in the right columns
...without loosing the horizontal integrity however.
Currently I do this manually with multiple auto filter and then copy past
the data in a new spreadsheet to construct a little excerpt for the business
on hand with a particular person when and if needed. Then I obtain ...tediously

A B C D E
F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? Red! =Pending <ASAP>
3 Johann 18/02/06 where? Blue! =positiv <not
acceptable>

It would be handy to do this one's and forever for the entire spreadsheet
with a vba routine if at any possible given the chaos!!!
The only consistency throughout the whole spreadsheet is that
names are kept under NAMES and all the items summed up in the row after
that particular name( say in line 6 for John...) has been performed at that
particular date (who appears in E and should be in C6) for that person
....so... should be kept together in the new spreadsheet
If , of course, somebody could write a routine to clean up he entire mess
that would be fantastic; but I would already be happy if I could sort out
automatically a name and date consistently with the rest of the horizontal
rows like this:
A B C D
E F
1 NAME DATE QUEST? ANSWER SOLUTION COMMENT
2 Johann 31/08/06 maybe? <ASAP> =pending red!
3 Johann 18/02/04 blue! where? =Positive
< not acceptable

The problem boils down to a date filtering, extracting loop routine in my
opinion ....
To make it even more difficult : the signs: ? < > = ! are just used here to
differentiate the nature of the data since this is a hypothetical table ...in
reality these data are large text strings, or when it comes to the heading
"=SOLUTION" sometimes a number format (this can give some surprises when
trying to extract dates in their number format)so this signs cannot be used
in a filtering vba routine as criteria to handle the restructuring of the
chaos
I hope somebody can help ...?
Thousand thanks in advance...

 

[stefsailor]

Sorry about the repetitive posting... MS announced that the message was not
posted so I did it again with the same content and they kept saying that
nothing was being posted that was yesterday night...
This morning I discovered that all this trials have actually been posted
So that must have triggered some chaos of alerts in your mail (if you do
that?)

Ok back to the problem, part of which was already beautifully handled with
your code Sandy...!!!

The result of your code showed me that the merging process of the different
sheets was not so messy after all
Like I said I am trying to mimic the existing sheet with an anodyne test
sheet
to make you understand what happens to the real one
(The real data are sensitive medical records of military personnel
taken in the field by the medics and merged with hospital and transportation
data
and they are in French Dutch and German ...)

The result of your rearranging macro made it clear that
some consistency might have been kept
I cannot post the result here because it does not look understandable with
copy past method from a txt file ...I do not know either how to post a
readable sample of a test sheet ...
So I took the liberty to email it to you in its original Excel form
Here’s the description however
The text strings (with non typical characteristics regarding the content:
only plain text) are finite somewhere between column C to G)
and here is maybe the good thing...?
They maintain their sequential integrity throughout the entire sheet
The QUESTION? text string (say in C) is always followed by it's ANSWER! (in
E consequently ...) They may not be under the right column headings ...
but on the other hand they are always kept together row wise...
The "question"-" answer" sequence is not disturbed
Also ...there is another sequential feature,
this time with a more remarkable format
I am talking about the TIME IN an TIME OUT of the interviews
(new columns which I did not talk about previously, thinking that my first
question was going to be impossible ....)

What I have to accomplish is
1/in A the name under NAME ...no problem in the spreadsheet
2/in B the (corresponding!) date under DATE...not a problem anymore thanks
too you
3/in C An open new column, say...: to put something new in
4/in D QUEST? with the question text string
5/in E ANSWER ! with the corresponding answer text string
6/in F TIME IN with the corresponding time (and format ) of the interview
start
7/In G TIME OUT with the end time (and format) of the interview
8/ in H which is a new column ...substraction of G-F which is of course the
duration of the interview
9/there are still more columns, like I said up to T, but this stuff is not
immediately needed in the task of our medical staff and I will probably be
able to treat that on my own, ones I got more grip on your code strategy to
handle this collumnshifts and realignment ...
So I am trying to figure out if your shifting routine could be adapted to
the characteristics of the remaining mess
Can one work-out in VBA to pick the " question " , " answer" (text string
format ) sequence
and to put it in their correct columns like you did with the date?
Can it also be done with the same code strategy based on time format?
knowing again that the needed sequence is intact?
And finally their is the issue of the duration and blank column to be
addressed.
I was reluctant to say it all, because I thought that the initial task ( the
date shift...)
was more or less impossible already...
So, I could not even start to dream about the rest ...
Even if this one is to hard thanks for what you did already
stef

 

[Sandy]

Give me a day or so to look it over, I'm glad that the first part
worked out for you...

Sandy