Moving Parsed Text from Rows to Columns

[dab]

I have thousands of rows of parsed text in single rows. What I have
been asked to do is now take part of each row (starting at column F)
and data in each of the next cells (the number of cells varies from row
to row) and copy if down into inserrted rows.

So instead of

Person1 Name Date Comment1 Comment2 Comment3 Comment4
Person2 Name Date Comment2 Comment2 Comment3
(the number of columns varies one I get to the first comment column)


They want it to look like this:

Person1 Name Date Comment1
Person1 Name Date Comment2
Person1 Name Date Comment3
Person1 Name Date Comment4
Person2 Name Date Comment1
Person2 Name Date Comment2
Person2 Name Date Comment3
etc...

Any ideas? I have thousands of rows already filled in as in the example
at the top.

Any macro recommendations would be greatefully appreciated.

 

[NickHK]

dab,
Could you use an SQL solution with a UNION of SELECT queries ?
e.g. SELECT PName, Date, Comment1 FROM MyTable WHERE IsNull(Comment1)=False
UNION
SELECT PName, Date, Comment2 FROM MyTable WHERE IsNull(Comment1)=False
etc..

NickHK

 

[dab]

This is a bit over my head. Can you provide me with a bit more detail
of how to implement this type of solution?

Dave B

 

[NickHK]

Dave,
You need to look at data>Get External Data>New Database Query, then follow
the wizard to get you data.
MS Query does not directly support UNION, but if you copy and paste
<SQL>
SELECT `Sheet1$`.PersonName, `Sheet1$`.Date, `Sheet1$`.Comment1
FROM `Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Comment1 Is Not Null)
UNION
.....
</SQL>
for however many Comments you have (changing Comments1 to Comments2 etc)

NickHK