Merging two spreadsheets using a script and creating html language

[apraily]

Hi, i have to merge this two stylesheet

The first one "DB" is a database of products, is a music shop.
As you can see i have 3 columns: the id of the product, the title, the price, and the tracklist.
Title and price are ok, but the column of the tracklist for now is empty.

The second one "TRACKS" is where my tracks are. Thre columns, the first one is the id of the product who refer, the second column is the number of the track, and the third column is the title.

I need to merge all the tracks on different lines for each product from "TRACKS" in a single cell of the tracklist in "DB"

The real BESTBESTBEST is to have a structure like this in the cell tracklist of "DB" :

<table>
<tbody>
<tr>
<td>1.</td>
<td>TITLE_1</td>
</tr>
<tr>
<td>2.</td>
<td>TITLE_2</td>
</tr>
<tr>
<td>3.</td>
<td>TITLE_3</td>
</tr>
</tbody>
</table>

It's really Tricky and i'm struggling for days to find a solutions, fact is that i don't know VB to make a script that can help me to do it...

Kind Regards,
Michele

[Charlize]

This function will put your structure in cell C with value of column A
Use it like : =mytracks(A2)
Setup of the cells : you need to allow the text to flow to another line.
If you want to use result, you need to paste as only text in D for example

Function mytracks(mycd As Range) As String
Dim mywb As Workbook, mywstracks As Worksheet
Dim mytrackno As String, notracks As Long
Dim looptracks As Long


Set mywb = ActiveWorkbook
Set mywstracks = mywb.Worksheets("TRACKS")
mytrackno = mycd.Value


notracks = mywstracks.Range("A" & Rows.Count).End(xlUp).Row
mytracks = "<table>" & Chr(10) & "<tbody>" & Chr(10)


If mytrackno = vbNullString Then
    mytracks = "no ID specified ..."
    Exit Function
Else
    For looptracks = 2 To notracks
        If mytrackno = mywstracks.Cells(looptracks, 1) Then
            mytracks = mytracks & "<tr>" & Chr(10) & "<td>" & mywstracks.Cells(looptracks, 2) & "</td>" & _
            Chr(10) & "<td>" & mywstracks.Cells(looptracks, 3) & "</td>" & Chr(10) & "</tr>" & Chr(10)
        End If
    Next looptracks
End If
mytracks = mytracks & "</tbody>" & Chr(10) & "</table>"
End Function

Charlize

[apraily]

This function will put your structure in cell C with value of column A
Use it like : =mytracks(A2)
Setup of the cells : you need to allow the text to flow to another line.
If you want to use result, you need to paste as only text in D for example

Function mytracks(mycd As Range) As String
Dim mywb As Workbook, mywstracks As Worksheet
Dim mytrackno As String, notracks As Long
Dim looptracks As Long


Set mywb = ActiveWorkbook
Set mywstracks = mywb.Worksheets("TRACKS")
mytrackno = mycd.Value


notracks = mywstracks.Range("A" & Rows.Count).End(xlUp).Row
mytracks = "<table>" & Chr(10) & "<tbody>" & Chr(10)


If mytrackno = vbNullString Then
    mytracks = "no ID specified ..."
    Exit Function
Else
    For looptracks = 2 To notracks
        If mytrackno = mywstracks.Cells(looptracks, 1) Then
            mytracks = mytracks & "<tr>" & Chr(10) & "<td>" & mywstracks.Cells(looptracks, 2) & "</td>" & _
            Chr(10) & "<td>" & mywstracks.Cells(looptracks, 3) & "</td>" & Chr(10) & "</tr>" & Chr(10)
        End If
    Next looptracks
End If
mytracks = mytracks & "</tbody>" & Chr(10) & "</table>"
End Function

Charlize

!Thanks, mate it works perfectly!!
Kind Regards!!