Transpose non organized data

[YasserKhalil]

Hello everyone
I have data in column A separated by blank row in between and I need to transpose the data to vertical
It would be easy if each group has the same number of items .. but the data is not organized well

Thanks advanced for help

[snb]

select the block, ctrl_C (copy), Pastespecial (with transpose).

[YasserKhalil]

Thanks a lot for reply
This pastes all the data in one row ..This is not original file, this is just sample file
The problem is how to put each proper data in proper column
For example : emails would be put in column H even if the information of each block is incomplete

[GTO]

Your example shows identical records. Can you give a more accurate example file, so we can see what issues there are to overcome?

[Paul_Hossler]

18304

Phone numbers and email addresses are easy enough, but cities without street addresses are trickier

I could test for the first piece (up to a space) is a number and assume that it's a street address, but that's not 100% reliable




Option Explicit

Sub TransPoseCopy()
Dim rowIn As Long, rowOut As Long, rowLast, colOut As Long

rowOut = 1
colOut = 3

With Worksheets("Sheet1")
rowLast = .Cells(.Rows.Count, 1).End(xlUp).Row

For rowIn = 1 To rowLast
If Len(.Cells(rowIn, 1).Value) > 0 Then
If .Cells(rowIn, 1).Value Like "(###) ###-####" Then
colOut = 7
ElseIf InStr(.Cells(rowIn, 1).Value, "@") <> 0 Then
colOut = 8
End If
.Cells(rowOut, colOut).Value = .Cells(rowIn, 1).Value
colOut = colOut + 1
Else
rowOut = rowOut + 1
colOut = 3
End If
Next rowIn

.Cells(1, 3).CurrentRegion.EntireColumn.AutoFit
End With

End Sub

[YasserKhalil]

Thanks a lot Mr. Paul for this great and working solution
exactly as I needed. I can adapt specific string now to specific columns (I was just in need to the idea)
Thank you very much for great help
Regards