Get unique combinations

[Longissimus]

I`m looking for a formula or anything really to get the following done.

I manually list stuff in Column from Sheet 2 and whether automatically or not I can get those entries sorted in unique combinations with entries based in B2 ,D2 ,F2 ,H2 & J2 in a different sheet -> sheet 1. These combinations must be unique but I dont want it to repeat combinations in the sense that once I have AAAAB I dont want AAABA, AABAA, ABAAA & BAAAA. Also it doesnt matter which of those I get so long as I get just one of them.

What`s do you think of this? This is just an example question,

Untitled.png

[MarvinP]

Hi Longissimus,

I'm afraid I only understand half of your question. I understand you don't want "AB" if you already have "BA" but I don't see the sheet1 vs sheet2 thing. Attach a sample workbook instead of a picture and we can work on it.

[Longissimus]

uploaded. apologies

[Longissimus]

It might need to be implemented as a macro. My knowledge is quite limited so by asking for a formula to achieve this I might be asking for something impossible or insanely complex using just formulae. I`m also willing to do some manual input as I understand sometimes we cannot easily operationalise things

[MarvinP]

Hi Longissimus,

I can do this problem by hand or by macro if you think my method is ok. First - you want only one COMBINATION (where order doesn't matter) of your letters in a row. To do this you need to remove duplicates. If you SORT each column individually this will make equal combinations exactly the same. Then copy all your data from columns to rows using PASTE TRANSFORM. Use the Advanced Filter Tool on the data tab and click the UNIQUE box. This will leave your answer in the filtered rows. You should set your data up without blank columns between other columns. See my attached.
Find Unique Combinations of Column Data.xlsx

[Longissimus]

I think there is a bit of a mix up. Am I correct to think that you assumed that I already had the columns with combinations (based on what you said in the excel sheet 'Problem is to find unique Combinations from Columns Above') and I just need to work on those?
Part of my requirement goes before that, I need those very combinations to be calculated from just 1 column found in another sheet (sheet2) and only then do I need the unique combinations (AAAAB, AAABA, AABAA etc... I only need one from these) placed in all the rows which you assumed I have.

(I thank you for taking the time to respond with that sheet )

[JeteMc]

If I understand correctly then this formula based proposal, which may be just on the barely on sane side, may help:
1. Cells R2:V2 are manually filled with 1's
2. R3:R33 are populated using: =IF(R2=V2,IF(R2=4,1+COUNTIFS(R$1:R1,3,R$2:R2,4),SUM(R2,1)),R2)
3. S3:V33 are populated using: =IF($R3<>$R2,1+COUNTIFS($R$2:$R2,4,$R$3:$R3,"<4"),R2)
4. X3:AB33 are populated using: =INDEX($O$2:$O$5,MATCH(R2,$P$2:$P$5,0))
Note that O2:O5 contain letters A through D and P2:P5 are numbered 1 through 4 respectively
5. AC2:AC33 are populated using: =X2&Y2&Z2&AA2&AB2
6. AD2:AD33 are populated using: =COUNTIFS(AC$2:AC2,AC2)
7. AF2:AF33 are populated using: =IFERROR(INDEX($AC$2:AC$33,AGGREGATE(15,6,(ROW(A$2:A$33)-ROW(A$1))/(AD$2:AD$33=1),ROWS(A$1:A1))),"")
8. AG2:AK33 are populated using: =MID($AF2,COLUMNS($A$1:A$1),1)
Let us know if you have any questions.

[Longissimus]

I thank you tonnes for this! I feel a thanks is not nearly enough. That was way beyond me. The least I can do is donate a very fine coffee to you and MarvinP somehow?

Conceptually it has worked but I changed the order of things because some of the countifs are returning #VALUE! (see below). Now I cannot get the index and match to work. I believe this is a simple fix but i cannot get my finger around it.

I have attached your file which shows my order below yours (everything is colour coded for easy following). You can see in the pink section I`m getting #N/A. This happens when formula =INDEX($O$2:$O$5,MATCH(AD36,$P$2:$P$5,0)) (under my pink section) references AD36 containing formula =MID($AC36,COLUMNS($A$1:A$1),1) (under the brown section). When i switch the reference in index/match (green secton, thats how you did it) from AD36 to R36 it will work. R36 contains =IF(R36=V36,IF(R36=4,1+COUNTIFS(R$35:R35,3,R$36:R36,4),SUM(R36,1)),R36) and is in the black section.

*I am attributing the #VALUE! return from countifs to the fact that their criteria cell has more than 255 characters. Whilst in the example we worked with simple letters (column A, B etc..), mine real life file I have long descriptions. So when using =X2&Y2&Z2&AA2&AB2 a statement over 255 chars is made. I changed the order so I can just get the huge sentences at the very end (hence countifs wouldnt act on them). See attached

[JeteMc]

Not sure that I totally follow, however the reason for the #N/A in the pink section is that the values in the brown section are text, which is why they align to the left. The MATCH function is not able to match the text values in AD36:AH51 to the numeric values in cells P2:P5. The easiest work around, and the one that I feel will work best for you purposes, is to convert the values in P2:P5 to text by prefixing the numbers with apostrophes i.e. P2: '1,P3: '2,P4 '3 and P5: '4. Once that is done the pink section will display letters. (I hope that makes sense).
Let us know if you have any questions.

[Longissimus]

One final thing, in the example you did there are only 4 letters where we are getting combinations from. It wont generate combinations with 5,6 etc.. In mine i have much more than just 4 letters.
I notice you have some formuale where 4 is hardcoded into it like =IF($R6<>$R5,1+COUNTIFS($R$2:$R5,4,$R$3:$R6,"<4"),R5). Can I just change those to whatever number? If yes there are some other formuales where other numbers are hardcoded and i do not know if these also need to be adjusted like this where 4 & 3 ard hardcoded =IF(R5=V5,IF(R5=4,1+COUNTIFS(R$1:R4,3,R$2:R5,4),SUM(R5,1)),R5)

[Longissimus]

Also I am going over the results. Here is a screenshot of the results from you last attachment. I testing the results and i there are results which i am missing. I can see why, my description of the end result was perhaps not good (I only meant to give an example that AAAAB is the same as ABAAA). If you look at each combination it has only 2 letters at most. I would need every combination of the letters like ABCDE, ABCDD, ABCCC and so on.Attachment 668443

[JeteMc]

This is a response to post #10.
The range O1:P5 has been converted to a table.
References in formulas to O2:O5 are changed to Table1[Column A]
Similarly references to P2:P5 are changed to Table1[Column 1]
Note that the numbers in Table1[Column 1] are not text as described in post #9, however the formula in the pink range (columns AP:AT) is modified to read: =INDEX(Table1[Column A],MATCH(VALUE(AD3),Table1[Column 1],0))
Test by adding a row to Table1 putting a letter [Column A] and 5 in [Column 1]
As to post #11 I'll give that a look later, however off the top of my head I would say that is a level of complexity that may be better suited to code.
Let us know if you have any questions.

[Longissimus]

I attached something I found on the internet, I`m sure it will help a lot. In column AA he`s got some formulas. I cannot even begin to understand.

The P column has the type output what I am looking for. It however will list ABC, ACB, BCA, BAC, CAB & CBA, I suppose if it`s not possible to just get 1 of those (any one) I am fine (am I taking on a herculean task by saying this? hehe) with manually finding
them myself and removing them. I suppose the issue right now would be to get that output (from column P) from just 1 column and not have to stay putting the same thing in each of the other columns (List 2, 3, 4 and so on).

[JeteMc]

That is a nice combinations generator. I deleted the values in A7:J9 and then type the letters A through I into cells A7:F9 as a test. In cell P7 I see A,D,G. I do not see that combination of letters repeated so I don't feel that the generator repeats the values that were originally displayed in A7:J9 either.
By the way I am going to put this into my toolbox, Thank You.

[Longissimus]

Would you be able to adapt it based on my requirements as I dont really know how it works.

[JeteMc]

I believe that the file is now doing what you want.
Note that D7:J10 are populated using: =$B7 so that what ever you type into B7:B10 will copy across.
Other than that I just copied existing formulas in column P and columns F:L on the HELP sheet down so that all 1024 combinations would display.
Let us know if you have any questions.

[Longissimus]

Literally no clue where to begin when it comes to copying it, like zero.
I uploaded an excel file which has the same format as what I want to do, would you be able to place them accordingly?
The green columns in sheet 2 would be the final place I would like the letter to be placed. I`m sorry if i`m coming off like an idiot but i`m literally blanking here.

[JeteMc]

I don't understand the way the "Combinations_Generator" works well enough to reproduce it in another file, but perhaps the following strategy would work.
Cut the "Values" columns, O:S from the Help sheet in the generator and put it on a separate sheet (TESTER Output), formatted to look like sheet 2 in the file you attached to post #17.
If that is acceptable, then we could attempt some formatting/hiding of the Combinations and Help sheets so that the file has the appearance of the TESTER file.
Let us know if you have any questions.

[Longissimus]

thank you very much

[JeteMc]

You're Welcome and thank you for the feedback. Let us know if you would like more help, however if the solution provided in post #18 is satisfactory, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.