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.

Host		Port1	Port2	Port3
Host1		1A	1B	1C
Host2

<tbody>
</tbody>

I would like to output the data as per below in another sheet.

Host	Output
Host1	Host1_1A
Host1	Host1_1B
Host1	Host1_1C
Host2	Host2_1A
Host2	Host2_1B
Host2	Host2_1C

<tbody>
</tbody>

Below is other sample on the data.

<tbody>
</tbody>

Host		Port1	Port2	Port3
Host1		1A	1B
Host2
Host3

<tbody>
</tbody>

If the Column in Port is empty it will skipped to the next host and loop with the available port.

Host	Output
Host1	Host1_1A
Host1	Host1_1B
Host2	Host2_1A
Host2	Host2_1B
Host3	Host3_1A
Host3	Host3_1B

<tbody>
</tbody>

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