Help using Transpose

[vinhdang]

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
(e-mail address removed)

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

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 | (e-mail address removed)
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 | (e-mail address removed)

and so forth.

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

 

[Bernie Deitrick]

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).EntireRow.Delete
Range("A:A").Delete

End Sub

 

[vinhdang]

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 | | (e-mail address removed)
(987)654-321 | | (e-mail address removed)


And so on...

 

[vinhdang]

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

 

[vinhdang]

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

 

[Tom Ogilvy]

Just curious; so why did you say:

Is this possible without VisualBasic?

 

[Dave Peterson]

You have another reply at your other post.

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
(e-mail address removed)

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

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 | (e-mail address removed)
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 | (e-mail address removed)

and so forth.

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