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

What I have				What I want
List A		List B		List C
Person		Access needed		Person 1	Access needed
Person 1		Access one		Person 1	Access one
Person 2		Access six		Person 1	Access six
Person 3		Access abc		Person 1	Access abc
Person 4		Access XYZ		Person 1	Access XYZ
Person 5		Access 5
Person 6		Access 15		Person 2	Access needed
Person 1000				Person 2	Access one
				Person 2	Access six
				Person 2	Access abc
					Access XYZ
				Person 3	Access needed
				Person 3	Access one
				Person 3	Access six
				Person 3	Access abc
				Person 3	Access XYZ
				Person 1000	Access needed
				Person 1001	Access one
				Person 1002	Access six
				Person 1003	Access abc
				Person 1004	Access XYZ
				Person 1005	Access 5
				Person 1006	Access 15
				Person 1007	Access LAST

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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