| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to pick out unique components in a list with unique and common | Excel Discussion (Misc queries) | |||
| Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
| function to retrieve a list of unique characters from a column | Excel Worksheet Functions | |||
| Counting unique combinations in two columns | Excel Discussion (Misc queries) | |||
| Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions | |||
![scottgorilla[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
