Simplifying information from a website map Hello all, I created a website map wi

Question

Simplifying information from a website map

Hello all,

I created a website map with some hierarchy invovled. basically I put the home page title in cell A1 and then the links form theat page starting in b2, etc. etc.

Now to simplify analysis of the website (long story as to why I am doing this) I figured it would help analyze the data to import it to access. IS there any way to take data that looks like this:

Shop

and make it look like this:

If there are ways to do this in excel or Access that would be great

Level 1 Level 2 Level 3 Home Tools Calculator Communications Account Setting Online Tutorial Quick Links PPACA Contact Us New Products Doctors Near You

Shop

Explanation / Answer

Easy enough, assuming that the URL and Item Type columns are always the last two columns:

Sub Reorg3()
    Dim i As Long, j As Long
    Dim nRows As Long
    Dim nCols As Long
'
    nRows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
    nCols = ActiveSheet.Range("A1").CurrentRegion.Columns.Count - 2
'
    ActiveSheet.Columns(nCols + 1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'
    For i = 2 To nRows
        For j = 1 To nCols
            If (ActiveSheet.Cells(i, j) <> "") Then
                If (j <> 1) Then ActiveSheet.Cells(i, 1).Value = ActiveSheet.Cells(i, j).Value
                ActiveSheet.Cells(i, nCols + 1).Value = j
                Exit For
            End If
        Next j
    Next i
    ActiveSheet.Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, nCols)).EntireColumn.Delete
    ActiveSheet.Columns("A:A").EntireColumn.AutoFit
    ActiveSheet.Range("A1") = "Title"
    ActiveSheet.Range("B1") = "Level"
End Sub

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---