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...Ill 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" wrote:

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

"Sandy" wrote:

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 wrote:
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...