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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]No.[/TD]
[TD]Description[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Date[/TD]
[TD]Attachment Desc.[/TD]
[TD]No. of Attach[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Excavations[/TD]
[TD]Contr1[/TD]
[TD]Owner[/TD]
[TD][/TD]
[TD]Employment contract.pdf
Bill of quantitaty[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Mechanical[/TD]
[TD]Contr2[/TD]
[TD]Owner[/TD]
[TD][/TD]
[TD]BOQ.xlsx
Shop Drawings.dwg
Work plan.xlsx[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Electrical[/TD]
[TD]Contr2[/TD]
[TD]Owner[/TD]
[TD][/TD]
[TD]BOQ.xlsx
Shop Drawings.dwg
Work plan.xlsx[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]No. from Table1[/TD]
[TD]Description[/TD]
[TD]File name[/TD]
[TD]Extension[/TD]
[TD]Hyperlink[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]No. from Table 1[/TD]
[TD]Description[/TD]
[TD]File name[/TD]
[TD]Extension[/TD]
[TD]Hyperlink[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Employment contract[/TD]
[TD][/TD]
[TD]pdf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Bill of quantitaty[/TD]
[TD][/TD]
[TD]xlsx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]BOQ[/TD]
[TD][/TD]
[TD]xlsx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Shop Drawings[/TD]
[TD][/TD]
[TD]dwg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Work plan[/TD]
[TD][/TD]
[TD]xlsx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]BOQ[/TD]
[TD][/TD]
[TD]xlsx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Shop Drawings[/TD]
[TD][/TD]
[TD]dwg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Work plan[/TD]
[TD][/TD]
[TD]xlsx[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
No. from Table 1	Description	File name	Extension	Hyperlink
Excavations	Employment contract	pdf	Employment contract.pdf
Excavations	Bill of quantitaty	xlsx	Bill of quantitaty.xlsx
Mechanical	BOQ	xlsx	BOQ.xlsx
Mechanical	Shop Drawings	dwg	Shop Drawings.dwg
Mechanical	Work plan	xlsx	Work plan.xlsx
Electrical	BOQ	xlsx	BOQ.xlsx
Electrical	Shop Drawings	dwg	Shop Drawings.dwg
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]33[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]33[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]35[/TD]

</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.