Loop through all the data and display them in proper format.

[faizal Izham]

I would like to seek help from the expert in Mr.Excel with my issue below.

i have some sample data as per below. Host is in 1 table and Port is on another table.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Host[/TD]
[TD][/TD]
[TD]Port1[/TD]
[TD]Port2[/TD]
[TD]Port3[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]1B[/TD]
[TD]1C[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to output the data as per below in another sheet.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Host[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD]Host1_1A[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD]Host1_1B[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD]Host1_1C[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD]Host2_1A[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD]Host2_1B[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD]Host2_1C[/TD]
[/TR]
</tbody>[/TABLE]

Below is other sample on the data.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Host[/TD]
[TD][/TD]
[TD]Port1[/TD]
[TD]Port2[/TD]
[TD]Port3[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]1B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Host3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the Column in Port is empty it will skipped to the next host and loop with the available port.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Host[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD]Host1_1A[/TD]
[/TR]
[TR]
[TD]Host1[/TD]
[TD]Host1_1B[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD]Host2_1A[/TD]
[/TR]
[TR]
[TD]Host2[/TD]
[TD]Host2_1B[/TD]
[/TR]
[TR]
[TD]Host3[/TD]
[TD]Host3_1A[/TD]
[/TR]
[TR]
[TD]Host3[/TD]
[TD]Host3_1B[/TD]
[/TR]
</tbody>[/TABLE]

Hope someone can help me with the formula to automatically loop around the data.

Thank You in advance for helping.

 

[oldbrewer]

in the top table, there is nothing against Host2, so why does it have 1A 1B 1C next to it in the second table ?

 

[faizal Izham]

Hi Oldbrewer,

actually the Host table and port table two seperate table. Each host will have to refer to port table to determine how many port the host have.
hope that helps.

Thanks in advance.

 

[oldbrewer]

it is not clear to me which tables are "information" tables and which table is your desired output. You refer to port table - which is it - and how does host 3 get 1a and 1b next to it ?

 

[Rick Rothstein]

Give this macro a try (change the output sheet name as required)...

[table="width: 500"]
[tr]
	[td]Sub HostPortTable()
  Dim R As Long, C As Long, X As Long
  Dim Hosts As Variant, Ports As Variant, HostPort As Variant
  Hosts = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value
  Ports = Range("C2", Range("C2").End(xlToRight)).Value
  ReDim HostPort(1 To UBound(Hosts, 1) * UBound(Ports, 2), 1 To 2)
  For R = 1 To UBound(Hosts, 1)
    For C = 1 To UBound(Ports, 2)
      X = X + 1
      HostPort(X, 1) = Hosts(R, 1)
      HostPort(X, 2) = Hosts(R, 1) & "_" & Ports(1, C)
    Next
  Next
  Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]").Range("A1").Resize(UBound(HostPort), 2) = HostPort
End Sub[/td]
[/tr]
[/table]

 

[faizal Izham]

Hi Rick,

Thanks for your posting. it helps to get things done