I am not sure that I understand exactly what the output needs to look like.
Even though the source data has broken up somewhat in this posting I believe
that I have established what it should look like. That are several rows for
each record and each record is identified by Name, Address and ID# and the
following rows with blanks for Name, Address and ID# belong to that same
record. Is this corect?

Am I correct in my understanding that you want the Line item # and Item Des
to be across the page on the same row as the Name, Address and ID# so there
is only one row per record? If so, what happens to Other1, Other2 etc; do
they have to be transposed to the one row also?

If my understanding is not correct then can you post an example of what you
want the output to look like for the first record.

Your quote: "There are Col F and Col G (more than 2 columns) following the
Line Item # and Item Des, which will be overwrite after transpose, if not
done correctly."
Not too hard to overcome. Can just look for the max (highest number) in the
Line Item# and insert/allow sufficient blank columns.

I suggest that the output be to a separate new worksheet. My reason is to
leave the source untouched so that you can compare source to output to
confirm that is what you want.

--
Regards,

OssieMac


"Neon520" wrote:

Hi Everyone,

I'm dealing with an odd data set here that need to be transpose some how,
here is the sample of the data set:

Col A | Col B | Col C | Col D | Col E | Col F |
Col G |
Name | Address | ID# | Line Item # | Item Des | Other1 | Other2 |
John | 123 street | 147 | 1 | s.thing1 | note1.1 |
note1.2 |
(blank)| (blank)| (blank)| 2 | s.thing2 | note2.1 |
note2.2 |
(blank)| (blank)| (blank)| 3 | s.thing3 | note3.1 |
note3.2 |
(blank)| (blank)| (blank)| 4 | s.thing4 | note4.1 |
note4.2 |
SMITH | 369 street | 148 | 1 | s.thing1 | note1.1 |
note1.2 |
(blank)| (blank)| (blank) | 2 | s.thing2 | note2.1 |
note2.2 |
(blank)| (blank)| (blank) | 3 | s.thing3 | note3.1 |
note3.2 |


What I need to do is to get rid of all the blank cell and Transpose the Col
D and E (Line Item # and Item Des) to be in a column format instead.

That problem:
Line item # and Item Des are varies from name to name.
There are Col F and Col G (more than 2 columns) following the Line Item #
and Item Des, which will be overwrite after transpose, if not done correctly.

Does anyone have a solution to this without having to Copy Paste Special
Transpose the data Name by Name Manually? I'm talking about thousands of
record here.

Thank you for any advice or help.

Neon520