Creating Combinations from Two Lists
 

I have data in two lists on two different worksheets. The data is laid out as:

Worksheet A
A
B
C

Worksheeet B
X
Y
Z

I need to define the relationship between each element in a vertical
setting, In the past I have used cut and paste but am hoping to write a macro
that would create the relationships for me. For example:

Col A Col B Col C
A X
A Y
A Z
X A
Y A
Z A

Does anyone have any thoughts if this is feasible or not?

Thanks!

I think you'll have to share how you came up with the relationships in your
sample data.

I don't see anything that could help me figure it out.



jlburak wrote:

I have data in two lists on two different worksheets. The data is laid out as:

Worksheet A
A
B
C

Worksheeet B
X
Y
Z

I need to define the relationship between each element in a vertical
setting, In the past I have used cut and paste but am hoping to write a macro
that would create the relationships for me. For example:

Col A Col B Col C
A X
A Y
A Z
X A
Y A
Z A

Does anyone have any thoughts if this is feasible or not?

Thanks!

--

Dave Peterson

Sorry relationships is probably the wrong word to use. For something specific
to my business, I need to submit a request to a team that basically says what
product one person can transfer to from another. At the end of the day I need
to supply to the team that updates a table an excel file in the format

Column A Column B Column C
Orig Product Allow/Don't Allow Desitnation Product

Column B must be manual, but I was hoping there might be a way to
pre-populate the template with A and C so I didn't have to copy and paste so
many times.

Thanks for your help.



"Dave Peterson" wrote:

I think you'll have to share how you came up with the relationships in your
sample data.

I don't see anything that could help me figure it out.



jlburak wrote:

I have data in two lists on two different worksheets. The data is laid out as:

Worksheet A
A
B
C

Worksheeet B
X
Y
Z

I need to define the relationship between each element in a vertical
setting, In the past I have used cut and paste but am hoping to write a macro
that would create the relationships for me. For example:

Col A Col B Col C
A X
A Y
A Z
X A
Y A
Z A

Does anyone have any thoughts if this is feasible or not?

Thanks!

--

Dave Peterson

I'm still not sure what you want, but it kind of sounds like you want:

For every entry in column A of sheet1, you want all the entries in column A of
sheet2.

So if you had

in sheet1
a
b
c
d

and in sheet2

1
2
3
4

you'd end up with

a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4
d 1
d 2
d 3
d 4

Is that close?

If yes:

Option Explicit
Sub testme01()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim newWks As Worksheet
Dim wks1Rng As Range
Dim wks2Rng As Range
Dim myCell As Range
Dim destCell As Range
Dim CountOfEntriesInWks2

Set wks1 = Worksheets("sheet1")
Set wks2 = Worksheets("sheet2")
Set newWks = Worksheets.Add

With wks1
Set wks1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With wks2
Set wks2Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
CountOfEntriesInWks2 = wks2Rng.Cells.Count
End With

Set destCell = newWks.Range("a1")
For Each myCell In wks1Rng.Cells
destCell.Resize(CountOfEntriesInWks2, 1).Value _
= myCell.Value
destCell.Offset(0, 2).Resize(CountOfEntriesInWks2, 1).Value _
= wks2Rng.Value
Set destCell = destCell.Offset(CountOfEntriesInWks2, 0)
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



jlburak wrote:

Sorry relationships is probably the wrong word to use. For something specific
to my business, I need to submit a request to a team that basically says what
product one person can transfer to from another. At the end of the day I need
to supply to the team that updates a table an excel file in the format

Column A Column B Column C
Orig Product Allow/Don't Allow Desitnation Product

Column B must be manual, but I was hoping there might be a way to
pre-populate the template with A and C so I didn't have to copy and paste so
many times.

Thanks for your help.

"Dave Peterson" wrote:

I think you'll have to share how you came up with the relationships in your
sample data.

I don't see anything that could help me figure it out.



jlburak wrote:

I have data in two lists on two different worksheets. The data is laid out as:

Worksheet A
A
B
C

Worksheeet B
X
Y
Z

I need to define the relationship between each element in a vertical
setting, In the past I have used cut and paste but am hoping to write a macro
that would create the relationships for me. For example:

Col A Col B Col C
A X
A Y
A Z
X A
Y A
Z A

Does anyone have any thoughts if this is feasible or not?

Thanks!

--

Dave Peterson


--

Dave Peterson