|
Need possible combinations of 7 unique characters.
Ok I have seven unique text items in two columns and need a formula that will
give me all the possible combinations in a list. The columns are as follows. Column 1 KEY KNOB 2KEY 2KNOB <blank Column 2 KEY KNOB KK 2KK 2KEY 2KNOB <blank What I am looking for is a way to list in two columns the unique combinations of Column1 & Column2 so I can insert a third column next to give a value to be used in a VLOOKUP formula. Thank You in advance |
Need possible combinations of 7 unique characters.
Hi,
As a first stab at it try this. Right click your sheet tab, view code and paste this in and run it Sub serviant() x = 1 lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrangeA = Range("A1:A" & lastrowA) Set myrangeB = Range("B1:B" & LastrowB) For Each c In myrangeA For Each d In myrangeB If c.Value < d.Value Then Cells(x, 3).Value = c Cells(x, 4).Value = d x = x + 1 End If Next Next End Sub Mike "scottgorilla" wrote: Ok I have seven unique text items in two columns and need a formula that will give me all the possible combinations in a list. The columns are as follows. Column 1 KEY KNOB 2KEY 2KNOB <blank Column 2 KEY KNOB KK 2KK 2KEY 2KNOB <blank What I am looking for is a way to list in two columns the unique combinations of Column1 & Column2 so I can insert a third column next to give a value to be used in a VLOOKUP formula. Thank You in advance |
Need possible combinations of 7 unique characters.
MikeH,
It worked.... why and how I have no idea but it worked and I surely do appreciate it. Thank You, Scott "Mike H" wrote: Hi, As a first stab at it try this. Right click your sheet tab, view code and paste this in and run it Sub serviant() x = 1 lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrangeA = Range("A1:A" & lastrowA) Set myrangeB = Range("B1:B" & LastrowB) For Each c In myrangeA For Each d In myrangeB If c.Value < d.Value Then Cells(x, 3).Value = c Cells(x, 4).Value = d x = x + 1 End If Next Next End Sub Mike "scottgorilla" wrote: Ok I have seven unique text items in two columns and need a formula that will give me all the possible combinations in a list. The columns are as follows. Column 1 KEY KNOB 2KEY 2KNOB <blank Column 2 KEY KNOB KK 2KK 2KEY 2KNOB <blank What I am looking for is a way to list in two columns the unique combinations of Column1 & Column2 so I can insert a third column next to give a value to be used in a VLOOKUP formula. Thank You in advance |
Need possible combinations of 7 unique characters.
glad I could help
"scottgorilla" wrote: MikeH, It worked.... why and how I have no idea but it worked and I surely do appreciate it. Thank You, Scott "Mike H" wrote: Hi, As a first stab at it try this. Right click your sheet tab, view code and paste this in and run it Sub serviant() x = 1 lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrangeA = Range("A1:A" & lastrowA) Set myrangeB = Range("B1:B" & LastrowB) For Each c In myrangeA For Each d In myrangeB If c.Value < d.Value Then Cells(x, 3).Value = c Cells(x, 4).Value = d x = x + 1 End If Next Next End Sub Mike "scottgorilla" wrote: Ok I have seven unique text items in two columns and need a formula that will give me all the possible combinations in a list. The columns are as follows. Column 1 KEY KNOB 2KEY 2KNOB <blank Column 2 KEY KNOB KK 2KK 2KEY 2KNOB <blank What I am looking for is a way to list in two columns the unique combinations of Column1 & Column2 so I can insert a third column next to give a value to be used in a VLOOKUP formula. Thank You in advance |
Need possible combinations of 7 unique characters.
Mike,
Found one problem after further investigation and after putting it into my wkst it does not give me duplicates side be side ie., knob...knob,key...key, any suggestions??? Thanks, Scott "Mike H" wrote: glad I could help "scottgorilla" wrote: MikeH, It worked.... why and how I have no idea but it worked and I surely do appreciate it. Thank You, Scott "Mike H" wrote: Hi, As a first stab at it try this. Right click your sheet tab, view code and paste this in and run it Sub serviant() x = 1 lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrangeA = Range("A1:A" & lastrowA) Set myrangeB = Range("B1:B" & LastrowB) For Each c In myrangeA For Each d In myrangeB If c.Value < d.Value Then Cells(x, 3).Value = c Cells(x, 4).Value = d x = x + 1 End If Next Next End Sub Mike "scottgorilla" wrote: Ok I have seven unique text items in two columns and need a formula that will give me all the possible combinations in a list. The columns are as follows. Column 1 KEY KNOB 2KEY 2KNOB <blank Column 2 KEY KNOB KK 2KK 2KEY 2KNOB <blank What I am looking for is a way to list in two columns the unique combinations of Column1 & Column2 so I can insert a third column next to give a value to be used in a VLOOKUP formula. Thank You in advance |
| All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com