Moving Parsed Text from Rows to Columns
 

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.

Moving Parsed Text from Rows to Columns
 

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" wrote in message
oups.com...
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.

Moving Parsed Text from Rows to Columns
 

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

Moving Parsed Text from Rows to Columns
 

Dave,
You need to look at dataGet External DataNew 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

"dab" wrote in message
ups.com...
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