?
Solved

Vertical list to table with hyperlink extraction

Posted on 2011-02-10
6
Medium Priority
?
258 Views
Last Modified: 2012-05-11
I have a list imported from web page (Sheet 2 in the file). I need to parse the data into a table in Sheet 3. Col. A = A7, A15, A23... in Sheet 2; Col. B = hyperlink of A7, A15, A23... in Sheet 2; Col. C= the number in A4, A12, A20... in Sheet 2; Col. D= A5, A13, A21... in Sheet 2; Col. E= the number of A8, A16, A24... in Sheet 2; Col. F= hyperlink of A8, A16, A24... in Sheet 2
As you can see everything is 8 incremental. I have the first two rows in Sheet 3 manually copied.
Can someone help me to build a VBA? Thanks
web-import.xls
0
Comment
Question by:mmcompact
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:dzenar
ID: 34866083
I think I almost understand the problem - One question -

What do you mean by 'hyperlink of...' do you mean hyperlink TO?
0
 

Author Comment

by:mmcompact
ID: 34866144
yes, so eg. A2 is "sparkzck" from A7 in Sheet2, B2 is "http://store.taobao.com/shop/view_shop.htm?user_number_id=75765428", which is the address you will get when you click on A7 in Sheet2.
And also, when I say "the number of", it means I want to extract the numeric number from the string of whichever cell it belongs
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 34866164
Hi

Are you sure that the data is arranged properly in sheet 2?

Click the button on Sheet 2 for the results.

Sid

Code Used


Private Sub CommandButton1_Click()
    Dim i As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long
    
    Set ws1 = Sheets("Sheet2")
    Set ws2 = Sheets("Sheet3")
    
    ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For i = 1 To ws1LastRow Step 8
        On Error Resume Next
        ws2.Range("A" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Value
        ws2.Range("B" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Hyperlinks(1).Address
        ws2.Range("C" & ws2LastRow).Value = OnlyNumbers(ws1.Range("A" & i).Offset(3).Value)
        ws2.Range("D" & ws2LastRow).Value = ws1.Range("A" & i).Offset(4).Value
        ws2.Range("E" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Value
        ws2.Range("F" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Hyperlinks(1).Address
        ws2LastRow = ws2LastRow + 1
        On Error GoTo 0
    Next
End Sub

Function OnlyNumbers(strInput As String) As String
    Dim strChar As String, strOutput As String
    strOutput = ""

    For i = 1 To Len(strInput)
        strChar = Mid(strInput, i, 1)
        If (IsNumeric(strChar)) Then
            strOutput = strOutput & strChar
        End If
    Next i
    OnlyNumbers = strOutput
End Function

Open in new window

web-import-1.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mmcompact
ID: 34866297
@SiddharthRout

Here's the page I want to populate the table from. Is there a way to solve this problem?
http://s.taobao.com/search?q=ugg&commend=all&style=list&tab=all&uniq=seller&ssid=s5-e&sort=sale-desc
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34866335
mmcompact: I have no issues in sharing points if someone has helped but I couldn't understand your choice for the "Assisted Solution"? How does that assist you in your query?

Sid
0
 

Author Comment

by:mmcompact
ID: 34872764
When I accepted your solution, the site didn't let me assign all the 500 points to you, I must pick one more. So I just put your 20 points to the other guy. I don't know another way to go around it. Kinda new here, sorry
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question