Multiple rows Map to Multiple rows

Terry_Carter

New Member
Joined
Sep 9, 2019
Messages
2
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi & welcome to MrExcel
Do you want the output in two columns, or the two values concatenated in one column?
 
Upvote 0
Ok, how about
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top