Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Lists | Links and Linking in Excel | |||
Aligning Two Lists in Excel | Excel Discussion (Misc queries) | |||
Creating Templates | Excel Discussion (Misc queries) | |||
comapre two lists | Excel Discussion (Misc queries) | |||
Creating a Date Selector in Excel VBA? | Excel Discussion (Misc queries) |