Hi,
Below is an example of what I am trying to accomplish, along with my current code at the bottom. I am trying to create a tool that will loop through multiple lists, generating each unique combination between them. The current code currently works close to expectation but there are 2 issues - A) the column "Output A & B" will show duplicates (understand I could remove them with another line of code but prefer avoiding that if possible) & B) if the column "Input C" is blank the code gets stuck in the loop. I think I might be missing adding in an IF statement somewhere, but am a little stuck.
What I am looking for is some help (suggested code, link to articles, etc) of how to get this current code to work 100% as expected? Extra credit but not necessary I'd love to figure out how to have the code also account for different orders between inputs A, B & C.
[TABLE="width: 418"]
<tbody>[TR]
[TD]Input A[/TD]
[TD]Input B[/TD]
[TD]Input C[/TD]
[TD][/TD]
[TD]Output A & B[/TD]
[TD]Output A&B&C[/TD]
[/TR]
[TR]
[TD]new[/TD]
[TD]car[/TD]
[TD]red[/TD]
[TD][/TD]
[TD]new car[/TD]
[TD]new car red[/TD]
[/TR]
[TR]
[TD]old[/TD]
[TD]shoes[/TD]
[TD][/TD]
[TD][/TD]
[TD]new shoes[/TD]
[TD]new shoes red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bike[/TD]
[TD][/TD]
[TD][/TD]
[TD]new bike[/TD]
[TD]new bike red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old car[/TD]
[TD]old car red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old shoes[/TD]
[TD]old shoes red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old bike[/TD]
[TD]old bike red[/TD]
[/TR]
</tbody>[/TABLE]
Below is an example of what I am trying to accomplish, along with my current code at the bottom. I am trying to create a tool that will loop through multiple lists, generating each unique combination between them. The current code currently works close to expectation but there are 2 issues - A) the column "Output A & B" will show duplicates (understand I could remove them with another line of code but prefer avoiding that if possible) & B) if the column "Input C" is blank the code gets stuck in the loop. I think I might be missing adding in an IF statement somewhere, but am a little stuck.
What I am looking for is some help (suggested code, link to articles, etc) of how to get this current code to work 100% as expected? Extra credit but not necessary I'd love to figure out how to have the code also account for different orders between inputs A, B & C.
[TABLE="width: 418"]
<tbody>[TR]
[TD]Input A[/TD]
[TD]Input B[/TD]
[TD]Input C[/TD]
[TD][/TD]
[TD]Output A & B[/TD]
[TD]Output A&B&C[/TD]
[/TR]
[TR]
[TD]new[/TD]
[TD]car[/TD]
[TD]red[/TD]
[TD][/TD]
[TD]new car[/TD]
[TD]new car red[/TD]
[/TR]
[TR]
[TD]old[/TD]
[TD]shoes[/TD]
[TD][/TD]
[TD][/TD]
[TD]new shoes[/TD]
[TD]new shoes red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bike[/TD]
[TD][/TD]
[TD][/TD]
[TD]new bike[/TD]
[TD]new bike red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old car[/TD]
[TD]old car red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old shoes[/TD]
[TD]old shoes red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]old bike[/TD]
[TD]old bike red[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Option Explicit
Sub New_Tool ()
Dim rng1 As Range, rng2 As Range, rng3 As RangeDim rngA As Range, rngB As Range, rngC As Range
Dim rngOut1 As Range, rngOut2 As Range
Set rng1 = Range("B5", Range("B5").End(xlDown))
Set rng2 = Range("c5", Range("c5").End(xlDown))
Set rng3 = Range("d5", Range("d5").End(xlDown))
Set rngOut1 = Range("F5")
Set rngOut2 = Range("G5")
For Each rngA In rng1.Cells
For Each rngB In rng2.Cells
For Each rngC In rng3.Cells
rngOut1 = rngA.Value & " " & rngB.Value
Set rngOut1 = rngOut1.Offset(1, 0)
rngOut2 = rngA.Value & " " & rngB.Value & " " & rngC.Value
Set rngOut2 = rngOut2.Offset(1, 0)
Next
Next
Next
End Sub