Sorting a Long List of Information into Columns

[ineedhelpbad]

First, I want to say thanks for clicking on the link. I really appreciate it. I have looked on youtube/articles and have tried desperately for hours to figure this out and I can't.

My information is in one single column and I have used the "text-to-column" feature but I'm afraid it wont work for this... Below is what my info looks like.

Aaron Panton
479-756-4880
Wells Fargo Home Mortgage
Area Manager
2737 E. Jackson Blvd
Jackson, MO 63755
Adam Caudle
479-684-3700
Signature Bank of Arkansas
3878 N. Crossover Rd., Suite 20
Fayetteville, AR 72703
E-mail
Adam Nobles
501-743-3742
Arvest Mortgage Company
Cabot, AR
E-mail

I need to take the NAME and put it into a "name" column. The ADDRESS and put it into an "address" column. The TELEPHONE NUMBER and put it into a "telephone column. The COMPANY and put it into a "company column. The EMAIL and put it into an "email" column (the email is linked).

All while there are over 100 of these contacts in one long single column. Any help would be greatly appreciated!

 

[Fernando Madrid]

We could get automatically what you want if we had the same number of fields for each contact and in the same position.
If we don't then we'll have mistakes.

Do you always have the same number of records for each contact?

If you don't we need previously to review and correct the column you have but it also depends on how many of your contacts have different structure.

 

[puru.sve]

Hi ,

Try this one , choose c1 and assign your heading onwords

Name

Address

Telephone

Company

E mail

<colgroup><col span="2" width="64"><col width="74"><col span="2" width="64"></colgroup><tbody>
</tbody>

Sub DivP()
k = 2

lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lrow
'    Set phrng = Range("A" & i & ":A" & lrow).Find(what:="-", after:=Cells(i, 1))
'    Set phrng2 = Range("A" & phrng.Row + 1 & ":A" & lrow).Find(what:="-", after:=Cells(phrng.Row, 1))
    
    If Cells(i, 1).Value Like "###-###-####" Then
    
    Set phrng = Cells(i, 1)
    Cells(k, 3).Value = phrng.Offset(-1, 0).Value
    Cells(k, 5).Value = phrng.Value
    Cells(k, 6).Value = phrng.Offset(1, 0).Value
    
    For j = i + 1 To i + 6
    
        If Cells(j, 1).Value Like "###-###-####" Then
        
            Set erng = Range("A" & i + 1 & ":A" & j - 2).Find(what:="E-mail")
            If Not erng Is Nothing Then
                Cells(k, 7).Value = Replace(erng, "E-mail", "", 1)
                e = -1
            Else
                e = 0
            End If
            Exit For
        End If
            
    Next j
    For Each r In Range("A" & i + 2 & ":A" & j - 2 + e)
        Cells(k, 4).Value = Cells(k, 4).Value & " " & r.Value
    Next r
    
    k = k + 1
    i = j - 1
    End If
Next

    
End Sub

 

[tmhopfne]

Send me spreadsheet via file sharing website (Google it if necessary!) and I'll finish for you quickly to learn

First, I want to say thanks for clicking on the link. I really appreciate it. I have looked on youtube/articles and have tried desperately for hours to figure this out and I can't.

My information is in one single column and I have used the "text-to-column" feature but I'm afraid it wont work for this... Below is what my info looks like.

Aaron Panton
479-756-4880
Wells Fargo Home Mortgage
Area Manager
2737 E. Jackson Blvd
Jackson, MO 63755
Adam Caudle
479-684-3700
Signature Bank of Arkansas
3878 N. Crossover Rd., Suite 20
Fayetteville, AR 72703
E-mail
Adam Nobles
501-743-3742
Arvest Mortgage Company
Cabot, AR
E-mail

I need to take the NAME and put it into a "name" column. The ADDRESS and put it into an "address" column. The TELEPHONE NUMBER and put it into a "telephone column. The COMPANY and put it into a "company column. The EMAIL and put it into an "email" column (the email is linked).

All while there are over 100 of these contacts in one long single column. Any help would be greatly appreciated!

 

[ineedhelpbad]

I want to thank you all for the responses, I am deeply appreciative! @Puru.sve I am not sure how to inject that code but I will look up to see how... Also I have attached the file https://drive.google.com/file/d/0B0zUjTS78U4paGRyTWIzUERhS0U/edit?usp=sharing

 

[puru.sve]

just open your excel where the data is.

Press Alt + F11 , the in left hand side you can see the sheet1 or sheet2. just double click on it and paste the code
then put your code in between and press F5. then you can see your result

 

[jsotola]

i hope that is not real data

here is one way to begin the data manipulation

put your data in column B

insert sequencial numbers for one to end of data into column A (1,2,3,4, ..... 3044)

sort both columns (A,B) on column B

now the data is in large blocks by type of data ( you will see what i mean )

highlight sections in column C and label by data type (record field name) ( name, address, city, position , company )

for eample ... using your data, the phone numbers end up in rows 1-381, select C1:C381, type "phone" and press ctrl-shft-enter (the whole range C1:C381 should contain "phone")

do the same for each type of data

since excel does autofill, after a while you only need to type only the first few letters and press ctrl-shft-enter

once all the datatypes are identified, resort column A,B,C on column A (ascending)

one other helper is to copy data to column D the do search for "*" Bold format and replace with "1" ( in column D only), then sort all four columns on column D

all the names will be together and can be labeled in column C in one go

now the data is back in the original sort order, and contains additional field name in third column

maybe someone else can come up with a macro to put the data in one-row-per-record

 

[kishorvimal]

May be you can check this video to learn Sorting and Filtering.

Excel 2007 2010 for Beginners #3 - Filter Sorting and Advance Filter - YouTube

<tbody>
</tbody>