VBA please Support

[dacha1122]

Hi All,

Im trying to find a way to extract longest unique combinations that will cover other combos Eg:




M1
C1
C2
C3



M2
C2
C3
C4



M3
C1
C4
C3



M5
C4
C3












End result will be M1 & M2 (covering C1-C4)

Im hitting my head like few days and i cannot make anything to work in VBA please help.

Thank you in advance.

[SamT]

M1 & M3 also work
M2 & M5 also work
Why not use one of them?

[dacha1122]

Nope they do not work cause M2 & M5 will miss C1 and M1 and M3 will be already covered if i chose M1 and M2 so it will repeat

[mikerickson]

I'm confused

M1&M2 duplicates C2 and C3

M1&M3 duplicates C1 and C3

Why is one acceptable, but the other not?

[mdmackillop]

I cannot make anything to work in VBA
I cannot understand what is a result outwith VBA. What are "longest unique combinations"?

[dacha1122]

Idea is to cover "Cs" with duplication's allowed and "Ms" cannot duplicate and i will change my previous comment now when i took a better look M1 & M3 can work also as they will cover all "Cs" but in this case M2 cannot repeat , sorry

[dacha1122]

im looking at Ms as combinations of Cs so i have a table with 188 Ms with a different combinations of Cs like i show in eg. so lets say now that we have M3 C1 C2 and M5 C1 C2 C3 and M6 C3 in it then M5 will be longest combo and covering M6 and M3 and i want M5 only to be extracted not M6 and M3

[SamT]

Sorry. Meant M1 & M5 will work and are shorter than M1 & M2

[dacha1122]

exacly....

[dacha1122]

You can make your own table just to fallow the same rules it can be also like this
M1 C1 C2 C3 C4 C5
M2 C2 C3
M3 C4 C5
M4 C1 C2 C3 C4
M5 C5 C6 C7 C8
M6 C6 C7

And result need to be M1 and M5

[SamT]

Gosh, I can make my own table that only needs M1 to satisfy your requirements.
Gosh, I can make my own table that needs all M's to satisfy your requirements.
Gosh, I can make my own table such that your requirements can not be satisfied.
Gosh, I can make my own table that has 15 different combinations that satisfy your requirements,

Gosh, I don't have a clue as to the restrictions and rules on your requirements.

We also don't have a clue what "Mn" and "Cn" stand for. What they stand for can, (probably does,) make a difference in what code is needed.

[dacha1122]

umm mb my English is a restriction of explaining you this on more simple way but i will try one more time cause this will help me i cannot even explain you how much :D

I need to extract Ms from a table to cover all Cs,but with smallest number of Ms i can

So basically i want to cover C1 to C8 (from the last table i sent) with less Ms possible

[SamT]

Will you settle for the first Combination found, regardless of how it's found?





With 188 +Rows and an unknown number of columns, this should all be done in Memory Arrays.
Create a Dictionary with all the C's as Keys, and all Items = "0" (False)

Create a ColumnCountArray(Rows.Count, 2), with (i,1) = Row.CountA, and (i, 2) = Row Number
Sort ColumnCountArray from Max (i, 1) to Min (i, 1).

Create DataArray of UsedRange.Offset(1, 1).Resize(Rows.Count - 1, Columns.Count - 1).

Loop thru ColumnCountArray using RowNumber to index DataArray Compare DataArray "Rows," "Column" Values to Dictionary, setting Item = 1, "True"

If all are not True, then
Reset Dictionary Items = 0, start over from ColumnCountArray(2)

If that fails, use three Row loop. Bwaa hahahaha:devil2: