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

Sandy

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