Help using Transpose

  • Thread starter Thread starter vinhdang
  • Start date Start date
V

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!
 
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
 
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...
 
Sorry, It was my mistake, I had forgotten to take out the blanks. The
macro works like a charm!
 
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!
Click to expand...
 
Back
Top