extract data from every other 5 rows

[rootbeer001]

i have a spreadsheet that has customer information, but unfortunately, it has seperated each customer in chucks of every 5 rows.

i need to come up with a way to extract names, addresses, and phone numbers into column format.

i've tried to create a new spreadsheet and seeing if autofill can iterate the formulas to increase every five cells, but no luck.

any suggestions would be appreciated.

 

[VoG]

Do you mean that it looks like

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:74px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Cust1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Cust2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Something</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


and you want

<b>Sheet5</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:90px;" /><col style="width:90px;" /><col style="width:90px;" /><col style="width:90px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Cust1</td><td >Something</td><td >Something</td><td >Something</td><td >Something</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Cust2</td><td >Something</td><td >Something</td><td >Something</td><td >Something</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[rootbeer001]

yes, pretty much just like that. thanks!

 

[GTO]

Greetings,

If this is just a one-time conversion - in a throwaway copy of your wb, try:

In a Standard Module:

Option Explicit
Sub transpose()
Dim lCnt As Long
Const lNumCustomers As Long = 200  '<---Change to number of customers or
                                    'higher if only blank rows below list.
    
    For lCnt = 1 To lNumCustomers
        Range(Cells(lCnt, 1), Cells(lCnt, 5)) = _
        Evaluate("=TRANSPOSE(" & _
                 Range(Cells(lCnt, 1), Cells(lCnt + 4, 1)).Address & _
                 ")")
        Range(Cells(lCnt + 1, 1), Cells(lCnt + 4, 1)).EntireRow.Delete
    Next
End Sub

Make sure to have the correct sheet active, as I left out qualifiers. As long as there's no data below the customers, overshooting the number would have no effect other than of course longer run time.

If this is some list you'll be getting daily/weekly, we can tack in a check to see how many records there are.

Hope this helps,

Mark

 

[VoG]

Alternative crappy method:

Sub test()
Dim LR As Long, i As Long, j As Long, k As Long, l As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add
With ws1
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR Step 5
        k = k + 1
        l = 0
        For j = i To i + 4
            l = l + 1
            ws2.Cells(k, l).Value = .Range("A" & j).Value
        Next j
    Next i
End With
End Sub