Repeated result based on counter and condition formula

[moks844]

Hi

I use 2 tables, one for entering data and another one for hyperlinking external files based on the data in table No.1

I will show the tables then follow it with the question.

Table 1

No.	Description	From	To	Date	Attachment Desc.	No. of Attach
33	Excavations	Contr1	Owner		Employment contract.pdf Bill of quantitaty	2
34	Mechanical	Contr2	Owner		BOQ.xlsx Shop Drawings.dwg Work plan.xlsx	3
35	Electrical	Contr2	Owner		BOQ.xlsx Shop Drawings.dwg Work plan.xlsx	3

<tbody>
</tbody>

Table 2

No. from Table1	Description	File name	Extension	Hyperlink

<tbody>
</tbody>

Now I'm looking for a formula or Macro so in Table 2 / column A the value will represent the first column in table 1 (Column A) and repeated as counted in the Table 1 / No. of attachments Column (Column G)

I hope I could explain my question looking to have your helps.
Thanks.

 

[moks844]

any helps ?

 

[DanteAmor]

You could put the expected result and explain how you got to the result.

 

[moks844]

You could put the expected result and explain how you got to the result.

OK what I want is that Table 2 have the following results:

No. from Table 1	Description	File name	Extension	Hyperlink
33	Employment contract		pdf
33	Bill of quantitaty		xlsx
34	BOQ		xlsx
34	Shop Drawings		dwg
34	Work plan		xlsx
35	BOQ		xlsx
35	Shop Drawings		dwg
35	Work plan		xlsx

<tbody>
</tbody>

 

[DanteAmor]

The hyperlink is directed to the same folder where you put the file with the macro.
I suppose that the names of the files are separated by an enter (chr 10) and that all the files have an extension.

This will be the result.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:155.88px;" /><col style="width:126.42px;" /><col style="width:173.94px;" /><col style="width:80.79px;" /><col style="width:255.68px;" /></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:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >No. from Table 1</td><td >Description</td><td >File name</td><td >Extension</td><td >Hyperlink</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#0000ff; text-decoration:underline; text-align:right; ">33</td><td >Excavations</td><td >Employment contract</td><td >pdf</td><td style="color:#0000ff; text-decoration:underline; ">Employment contract.pdf</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">33</td><td >Excavations</td><td >Bill of quantitaty</td><td >xlsx</td><td style="color:#0000ff; text-decoration:underline; ">Bill of quantitaty.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">34</td><td >Mechanical</td><td >BOQ</td><td >xlsx</td><td style="color:#0000ff; text-decoration:underline; ">BOQ.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">34</td><td >Mechanical</td><td >Shop Drawings</td><td >dwg</td><td style="color:#0000ff; text-decoration:underline; ">Shop Drawings.dwg</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">34</td><td >Mechanical</td><td >Work plan</td><td >xlsx</td><td style="color:#0000ff; text-decoration:underline; ">Work plan.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">35</td><td >Electrical</td><td >BOQ</td><td >xlsx</td><td style="color:#0000ff; text-decoration:underline; ">BOQ.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">35</td><td >Electrical</td><td >Shop Drawings</td><td >dwg</td><td style="color:#0000ff; text-decoration:underline; ">Shop Drawings.dwg</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">35</td><td >Electrical</td><td >Work plan</td><td >xlsx</td><td style="color:#0000ff; text-decoration:underline; ">Work plan.xlsx</td></tr></table>

Sub Repeated_result()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, lr As Long, wPath As String
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Rows("2:" & Rows.Count).ClearContents
    wPath = ThisWorkbook.Path & "\"
    
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        For Each f In Split(c.Offset(, 5), Chr(10))
            lr = Range("A" & Rows.Count).End(xlUp).Row + 1
            sh2.Range("A" & lr).Value = c.Value
            sh2.Range("B" & lr).Value = c.Offset(, 1).Value
            sh2.Range("C" & lr).Value = Left(f, InStrRev(f, ".") - 1)
            sh2.Range("D" & lr).Value = Mid(f, InStrRev(f, ".") + 1)
            sh2.Range("E" & lr).Value = f
            sh2.Hyperlinks.Add Anchor:=sh2.Range("E" & lr), Address:=wPath & f
        Next
    Next
End Sub

 

[moks844]

The hyperlink is directed to the same folder where you put the file with the macro.
I suppose that the names of the files are separated by an enter (chr 10) and that all the files have an extension.

This will be the result.

	A	B	C	D	E
1	No. from Table 1	Description	File name	Extension	Hyperlink
2	33	Excavations	Employment contract	pdf	Employment contract.pdf
3	33	Excavations	Bill of quantitaty	xlsx	Bill of quantitaty.xlsx
4	34	Mechanical	BOQ	xlsx	BOQ.xlsx
5	34	Mechanical	Shop Drawings	dwg	Shop Drawings.dwg
6	34	Mechanical	Work plan	xlsx	Work plan.xlsx
7	35	Electrical	BOQ	xlsx	BOQ.xlsx
8	35	Electrical	Shop Drawings	dwg	Shop Drawings.dwg
9	35	Electrical	Work plan	xlsx	Work plan.xlsx

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:155.88px;"><col style="width:126.42px;"><col style="width:173.94px;"><col style="width:80.79px;"><col style="width:255.68px;"></colgroup><tbody>
</tbody>

Sub Repeated_result()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, lr As Long, wPath As String
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Rows("2:" & Rows.Count).ClearContents
    wPath = ThisWorkbook.Path & "\"
    
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        For Each f In Split(c.Offset(, 5), Chr(10))
            lr = Range("A" & Rows.Count).End(xlUp).Row + 1
            sh2.Range("A" & lr).Value = c.Value
            sh2.Range("B" & lr).Value = c.Offset(, 1).Value
            sh2.Range("C" & lr).Value = Left(f, InStrRev(f, ".") - 1)
            sh2.Range("D" & lr).Value = Mid(f, InStrRev(f, ".") + 1)
            sh2.Range("E" & lr).Value = f
            sh2.Hyperlinks.Add Anchor:=sh2.Range("E" & lr), Address:=wPath & f
        Next
    Next
End Sub

I thank you very much for your great efforts.
Though I eventually guided into different approach using formulas. formulas could be better in understanding more than macros, thus just to spread the info, I found my solution in this article:
https://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html

 

[DanteAmor]

I am pleased to know that you found a solution, now you have macro and formula. Thanks for the feedback.