Multiple rows Map to Multiple rows

[Terry_Carter]

Hi this is my first post, firat of all thank you to eveyone here this site is always a great source of infiormation and help.

Here is my problem
I have a list of People and a list of items, each item has to aligned to the person - Multiple persons - Unique Item

Thanks in advance Terry​

List A

Person 1
Person 2

List B
Item 1
Item 2

Output
List c

Person 1 - Item 1
Person 1 - Item 2
Person 2 - Item 1
Person 2 - Item 2

etc



[TABLE="width: 490"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]What I have[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]What I want [/TD]
[/TR]
[TR]
[TD]List A [/TD]
[TD][/TD]
[TD]List B[/TD]
[TD][/TD]
[TD]List C [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person[/TD]
[TD][/TD]
[TD]Access needed[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Access needed[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD][/TD]
[TD]Access one[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Access one[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD][/TD]
[TD]Access six[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Access six[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD][/TD]
[TD]Access abc[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Access abc[/TD]
[/TR]
[TR]
[TD]Person 4[/TD]
[TD][/TD]
[TD]Access XYZ[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Access XYZ[/TD]
[/TR]
[TR]
[TD]Person 5[/TD]
[TD][/TD]
[TD]Access 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 6[/TD]
[TD][/TD]
[TD]Access 15[/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD]Access needed[/TD]
[/TR]
[TR]
[TD]Person 1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD]Access one[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD]Access six[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD]Access abc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Access XYZ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Access needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Access one[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Access six[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Access abc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Access XYZ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1000[/TD]
[TD]Access needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1001[/TD]
[TD]Access one[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1002[/TD]
[TD]Access six[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1003[/TD]
[TD]Access abc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1004[/TD]
[TD]Access XYZ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1005[/TD]
[TD]Access 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1006[/TD]
[TD]Access 15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1007[/TD]
[TD]Access LAST[/TD]
[/TR]
</tbody>[/TABLE]


Thanks Terry

 

[Fluff]

Hi & welcome to MrExcel
Do you want the output in two columns, or the two values concatenated in one column?

 

[Terry_Carter]

Two Columns please

 

[Fluff]

Ok, how about

Sub TerryCarter()
   Dim Rng As Range, Cl As Range
   
   Set Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      With Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Rng.Count)
         .Value = Cl.Value
         .Offset(, 1).Value = Rng.Value
      End With
   Next Cl
End Sub

This assumes the two lists are in col A & B starting in row 2 & will output into cols C & D