permutation and combination

%3CLINGO-SUB%20id%3D%22lingo-sub-2984784%22%20slang%3D%22en-US%22%3Epermutation%20and%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2984784%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%2C%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20you%20all%20are%20doing%20great!%3CBR%20%2F%3Ei%20have%20numbers%20in%20the%20column%20a%2C%20Now%20i%20want%20to%20calculate%20all%20the%20permutation%20and%20combination%20of%20all%20the%20pair%20of%203%20cells%20cells%20and%20there%20should%20be%20no%20repetitions%20in%20the%20same%2C%20Like%20i%20only%20need%20unique%20results%2C%20I%20am%20open%20to%20VBA%20and%20excel%20functions%20both.%3CBR%20%2F%3EWaiting%20for%20some%20experts%20response%2C%20From%20scratch%20i%20have%20given%20an%20example%20in%20column%20c.%3CBR%20%2F%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2984784%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2984871%22%20slang%3D%22en-US%22%3ERe%3A%20permutation%20and%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2984871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1189141%22%20target%3D%22_blank%22%3E%40yugal40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20You'll%20have%20to%20allow%20macros.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2987915%22%20slang%3D%22en-US%22%3ERe%3A%20permutation%20and%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2987915%22%20slang%3D%22en-US%22%3EI%20really%20appreciate%20your%20understanding%20of%20macros!%3CBR%20%2F%3E%3CBR%20%2F%3EReally%20thanks%20for%20your%20reply%2C%20It%20worked%20for%20me.%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20one%20more%20thing%20can%20we%20give%20an%20input%20instead%20of%20hard%20coding%203%20as%20a%20number%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20will%20make%20my%20task%20more%20easier%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%2C%3CBR%20%2F%3EHave%20a%20great%20day.%3C%2FLINGO-BODY%3E
Contributor

Hi Folks,

Hope you all are doing great!
i have numbers in the column a, Now i want to calculate all the permutation and combination of all the pair of 3 cells cells and there should be no repetitions in the same, Like i only need unique results, I am open to VBA and excel functions both.
Waiting for some experts response, From scratch i have given an example in column c.
Thanks

3 Replies

@yugal40 

See the attached version. You'll have to allow macros.

I really appreciate your understanding of macros!

Really thanks for your reply, It worked for me.

Just one more thing can we give an input instead of hard coding 3 as a number?

It will make my task more easier

Thanks again,
Have a great day.

@yugal40 

Change the Test macro as follows; the rest can remain the same:

Sub Test()
    Dim Num As Long
    Dim r As Long
    Dim LastRow As Long
    Num = Application.InputBox(Prompt:="How many items in each combination?", Default:=3, Type:=1)
    If Num < 2 Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    ReDim myArray(1 To LastRow)
    For r = 1 To LastRow
        myArray(r) = Range("A" & r).Value
    Next r
    Call CombosNoRep(myArray, Num)
    Application.ScreenUpdating = True
End Sub