Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    All Unique Values

    Hello,
    I have an Excel spreadsheet with a column of values (see below). Is there an Excel formula or simple way to identify ALL of the unique combinations aside from the 6 below (i.e., A,B or A,B,C or A,B,C,D,E,F, etc., etc.)? Any insight is much appreciated!


  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Not entirely sure what you expect

    But you may want to try the following Functions "COMBIN" or "PERMU"

    If not please send a simple workbook of the data and expected results.

    TD

  3. #3
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks TD. Let me explain further. Imagine I had a single column with different names:

    A1 - Brian
    A2 - Mike
    A3 -John
    A4 - Amy
    A5 - Lori

    5 records in total (I included the A1 - A5 to relate to the spreadsheet row). But basically it's 5 records. I'd like to see all the possible combinations from the 5 records. Something like this.

    Brian
    Mike
    John
    Amy
    Lori
    Brian,Mike
    Brian,John
    Brian,Amy
    Brian,Lori
    Brian,Mike,John
    Brian,Mike,Amy
    Brian,Mike,Lori

    and so on and so on.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    The VBA Code below is from JEZZA who posted it on the site. it's post 5680 and can be found by searching this forum for "Permutations".

    Put the Macro in the Module for the Workbook you want.

    Find a blank worksheet or a worksheet with Column A that is Blank

    Run the Macro GetString when the dialog box appears put one and only one letter for each person, your example
    BMJAL NOTE the Macro is limiteed to a maximum of 8 letters

    The Macro will output the permutations. You will need to use various stirng formulas to acheive the final desired results.

    The results will begin at Cell A1 and each row will show a possible combination.

    Dim CurrentRow
    Sub GetString()
    Dim InString As String
    Dim NewString As String
    InString = InputBox("Enter text:")

    ActiveSheet.Columns(1).Clear
    CurrentRow = 1
    If Len(InString) < 1 Then Exit Sub
    For i = 1 To Len(InString)
    NewString = Left(InString, i)
    If Len(InString) >= 8 Then
    MsgBox "Too many permutations!"
    Exit Sub
    Else

    Call Permute("", NewString)
    End If
    Next i
    End Sub
    Sub Permute(x As String, y As String)
    Dim i As Integer, j As Integer
    j = Len(y)
    If j < 2 Then
    Cells(CurrentRow, 1) = x & y
    CurrentRow = CurrentRow + 1
    Else
    For i = 1 To j
    Call Permute(x + Mid(y, i, 1), _
    Left(y, i - 1) + Right(y, j - i))
    Next
    End If
    End Sub
    Last edited by duthiet; 2012-01-20 at 18:20.

  5. #5
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you! I also found the following solution that worked perfectly:

    http://www.mrexcel.com/forum/showthread.php?t=438212

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •