All,
I have a thousand rows with data like so:
SOURCE DATA
Col 1 | Col 2 | Col 3 | Col4
Person 1| Book1|Book2|Book3
Person 2| Book1|Book2|
Person 3| Book1|Book2|Book3| Book4

I need to reorganize this data to transpose part of data (column 2 onwards) from rows into columns inserting the transposed rows so it looks like:
TARGET DATA
Col 1 | Col 2 |
Person 1| Book1|
Person 1| Book2|
Person 1| Book3|
Person 1| Book4|
Person 2| Book1|
Person 2| Book2|
Person 3| Book1|
Person 3| Book2|
Person 3| Book3|
Person 3| Book4|
The # of books per person can vary up to 8 books. So, I inserted 10 blank rows between each of the original rows. Then, copied records from each row and did a transpose paste into the blank rows I had inserted. Finally, deleted the remaining blanks.
There has to be a better way. Can someone help?