Contact list from Vertical to Horizontal so it can be imported to datebase software

[BRIXTON]

I have a list of 40k contacts listed vertically that I would like to convert to a horizontal list, so that I can then import it to database software. Problem is that not each contact has the same info. i.e some have phone numbers, some have titles, some have addresses, and some don't. See table below. I added and labeled columns A & B in the thought that I could use those numbers and labels as a reference to pull into columns J through P but came to a dead end. Would very much appreciate some guidance as this list is much too long to do manually. Also, FYI, I know very little VB so if that's the best solution, I would appreciate it if you could put in laymen's terms to the extent that it is possible...[TABLE="width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[TD]i
[/TD]
[TD]j
[/TD]
[TD]k
[/TD]
[TD]l
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]title
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]company
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]address
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]suite
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]city/state
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]Phone
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Desired Result:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]Title
[/TD]
[TD]Company
[/TD]
[TD]Address
[/TD]
[TD]Suite
[/TD]
[TD]City/State
[/TD]
[TD]Phone
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]SVP
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John Smith
[/TD]
[TD]SVP
[/TD]
[TD]Widget Manufacturing Co
[/TD]
[TD]123 Main Street
[/TD]
[TD][/TD]
[TD]Los Angeles, CA
[/TD]
[TD](555) 555-5555
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]3
[/TD]
[TD]Widget manufacturing Co
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]4
[/TD]
[TD]123 Main Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]6
[/TD]
[TD]Los Angeles, CA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]7
[/TD]
[TD](555) 555-5555
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Just to clarify something. You say that not each contact has the same info.

Does each contact still consist of seven rows? would there be blank values for the missing info or would the contact consist of less than seven rows?

 

[murphm03]

Copy the column,then click on a free cell that suits-go to paste special and tick transpose in the pop up (bottom right) and ok.There your done

 

[Comfy]

Copy the column,then click on a free cell that suits-go to paste special and tick transpose in the pop up (bottom right) and ok.There your done

For multiple records Transpose would just paste all values in the same row.
I think the OP wishes each record to be on its own row.

 

[Comfy]

You could try the following:

Sub ContactExtract()
Dim Contacts As Range
Dim i As Long, j As Long, Records As Long


Set Contacts = Selection
Records = 2


For i = 1 To Contacts.Cells.count Step 7
    For j = 1 To 6
        Sheets(2).Cells(Records, j).Value = Sheets(1).Cells(i + j - 1, 1).Value
    Next j
Records = Records + 1
Next i


End Sub

But I imagine like anything more info is required to tailor this to your needs.

Excel 2010

	A

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]SVP[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Widget manufacturing Co[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]123 Main Street[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Los Angeles, CA[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"](555) 555-5555[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Widget manufacturing Co[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]123 Main Street[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]Los Angeles, CA[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"](555) 555-5555[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]SVP[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]123 Main Street[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]Los Angeles, CA[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"](555) 555-5555[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]SVP[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]Widget manufacturing Co[/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]Los Angeles, CA[/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"](555) 555-5555[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA"]SVP[/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA"]Widget manufacturing Co[/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FAFAFA"]123 Main Street[/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA"](555) 555-5555[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA"]John Smith[/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA"]SVP[/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA"]Widget manufacturing Co[/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #FAFAFA"]123 Main Street[/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA"]Los Angeles, CA[/TD]

</tbody>

Sheet1​

Becomes:

Excel 2010
	A	B	C	D	E	F
1	Name	Title	Company	Address 1	Address 2	Phone Number
2	John Smith	SVP	Widget manufacturing Co	123 Main Street	Los Angeles, CA	(555) 555-5555
3	John Smith		Widget manufacturing Co	123 Main Street	Los Angeles, CA	(555) 555-5555
4	John Smith	SVP		123 Main Street	Los Angeles, CA	(555) 555-5555
5	John Smith	SVP	Widget manufacturing Co		Los Angeles, CA	(555) 555-5555
6	John Smith	SVP	Widget manufacturing Co	123 Main Street		(555) 555-5555
7	John Smith	SVP	Widget manufacturing Co	123 Main Street	Los Angeles, CA
Sheet2

 

[TheVillageIdiot]

VBA would be your best bet. I don't think you can transpose data easily using native Excel functionality and a formula solution would likely take forever to calculate.

Run the following code on a sample set of a copy of your data and report your results. You should have the data sheet active when you run it, as I only specified the data on the active sheet in the code.

Sub TransformData()
Dim xlApp As Excel.Application
Dim wkbk As Workbook, wks As Worksheet, NewWks As Worksheet
Dim i As Long, LR As Long, NR As Long, arr(1 To 7) As Long, arrData, SzArr As Long

Set xlApp = Excel.Application

With xlApp
    .ScreenUpdating = False
    .DisplayAlerts = False
    .StatusBar = False
End With

Set wkbk = ThisWorkbook
With wkbk
    Set wks = .ActiveSheet
    Set NewWks = .Worksheets.Add
    With NewWks
        .Cells(1, 1) = "ItemID"
        .Cells(1, 2) = "DataValue"
    End With
End With
    
    SzArr = UBound(arr) - LBound(arr) + 1
    For i = LBound(arr) To UBound(arr)
        arr(i) = i
    Next i
    
    With wks
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To LR  '' Change this to the row which starts the data table.  I assumed a header row
            xlApp.StatusBar = i & " of " & LR
            arrData = xlApp.Transpose(xlApp.Transpose(.Range(.Cells(i, 1), .Cells(i, SzArr)).Value))
            With NewWks
                NR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Cells(NR, 1).Resize(SzArr) = xlApp.Transpose(arr)
                .Cells(NR, 2).Resize(SzArr) = xlApp.Transpose(arrData)
            End With
        Next i
        
    End With

Erase arr
Erase arrData

With xlApp
    .ScreenUpdating = True
    .DisplayAlerts = True
    .StatusBar = False
End With

End Sub

 

[TheVillageIdiot]

My solution does the opposite of what you intended -- it takes a flat file format and puts it into a vertical database format.

 

[BRIXTON]

Each contact has a different amount of rows (7 or less).

Just to clarify something. You say that not each contact has the same info.

Does each contact still consist of seven rows? would there be blank values for the missing info or would the contact consist of less than seven rows?

 

[BRIXTON]

Would you mind explaining how I run this? Not sure how to see if your solution works