I need help with the transpose function. Currently I have 2000 columns
with data of people's names. For example

Jane Doe
Partner
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


Doe, John
Lawyer
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


I wish to transpose the data from a column to row with a delimiter
using that email address (because not all of the data has 6 rows of
data)

Is this possible without VisualBasic? Such as a function that will
notice the "@" symbol and transpose the next group of data into a new
row.

The output should be like this

Row One:
Jane Doe | Partner | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |

and so forth.

If you have any suggestions or websites I can check out, you have my
gratitude!

Vinhdang,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub MacroForVinhdang()
Dim myR As Range
Dim myC As Range
Dim myX As Range

Set myR = Range("A:A")

Set myC = myR.Find(What:="@", LookAt:=xlPart)

While Not myC Is Nothing
Set myX = Range(myC, myC.End(xlUp))
myX.Copy
myC(1, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
myX.ClearContents
Set myC = myR.FindNext
Wend

Range("B:B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
Range("A:A").Delete

End Sub


wrote in message ups.com...
I need help with the transpose function. Currently I have 2000 columns
with data of people's names. For example

Jane Doe
Partner
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


Doe, John
Lawyer
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


I wish to transpose the data from a column to row with a delimiter
using that email address (because not all of the data has 6 rows of
data)

Is this possible without VisualBasic? Such as a function that will
notice the "@" symbol and transpose the next group of data into a new
row.

The output should be like this

Row One:
Jane Doe | Partner | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |

and so forth.

If you have any suggestions or websites I can check out, you have my
gratitude!

Thank you so much for replying.

I tried the macro and it seemed to work but the output came out to
something similar as this (using columns A, B, and C)

(123)456-789 | |
(987)654-321 | |


And so on...

Sorry, It was my mistake, I had forgotten to take out the blanks. The
macro works like a charm!

Nevermind, the macro is perfect! I had not taken the blanks out which
caused the error

Just curious; so why did you say:

Is this possible without VisualBasic?

--
Regards,
Tom Ogilvy


" wrote:

Nevermind, the macro is perfect! I had not taken the blanks out which
caused the error

You have another reply at your other post.

wrote:

I need help with the transpose function. Currently I have 2000 columns
with data of people's names. For example

Jane Doe
Partner
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


Doe, John
Lawyer
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


I wish to transpose the data from a column to row with a delimiter
using that email address (because not all of the data has 6 rows of
data)

Is this possible without VisualBasic? Such as a function that will
notice the "@" symbol and transpose the next group of data into a new
row.

The output should be like this

Row One:
Jane Doe | Partner | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |

and so forth.

If you have any suggestions or websites I can check out, you have my
gratitude!

--

Dave Peterson