Find an optimal combination

Hi to everyone,

I'm new to this forum, so please forgive me if I post at the wrong place.

I have a longstanding problem that I have not been able to solve so far. I have tried many solutions, but none worked out as expected.

The problem is the following one: we use antibodies (Abs) for our experiments. Each Ab can be purchased coupled with different fluorochromes. We then use an instrument to detect each of them (if bound to cells) with band pass filters. When we design a new experiment, the various Abs to be used are mixed together, and hence, we cannot use 2 (or more) Abs with the same fluorochromes: one filter of detection must be paired with one Ab-fluorochrome conjugate (I hope I am clear enough).

Now, since our experiments are getting more and more complicated, that we have 17 detectors, and a lot of antibody combinations possible, it is becoming a nightmare to decide which is the optimal Ab combination to use.

I thought that an Excel macro could help to choose the best combination. The users would specify which Abs they want to use and for each, would provide the macro with the list of fluorochrome conjugates at our disposal. The macro would then return the optimal combination(s). I have attached a file with an example of Ab combination.

Many thanks for any help!!

Antonino

shg,

Thank you so much for helping me. As you correctly guessed, the numbers in row 2 are the wavelengths and bandwidths at which the fluorescences are collected for each laser. The fact that there are multiple fluorochromes listed for each detector (wavelength) comes from the fact that distinct fluorochromes can be detected by the same detector and hence should not be used together in a given combination: for instance, one cannot use a FITC-conjugated CD3 Ab together with an Alexa488-conjugated CD4 Ab.

I have tried to open the file that you have attached to your file but I get an error message (I am using Microsoft Excel 2004 v11.1 on mac, french version): 'Compilation error. Expected Line number or label or instruction or end of instruction' (translated from french). I would really appreciate again your help!!

With my very best regards

It seems that the compilation has a problem with the very beginning of the code:

Enum Col
Num = 1
Ab
Dsc
Use
End Enum

I have found a (very empirical) solution that I have attached to this post...The solution of shg is much more elegant though it doesn't list all possible combinations.

shg,

Thank you so much for all your efforts. With the new code, I get another compilation error 'argument already specified'. The highlighted code by the debugger is 'Sub GlowWorms()' (Line=Key2:=rInp(1, ColNum), Order1:=xlAscending, _)

Best regards,

Antonino

I understand that a new Microsoft Office for the Mac will be available before the end of the year. My understanding is that it will have VBA in it.! That may resolve part of this problem.

OK, I have planned to switch to the new MSOffice for mac. That MS had removed VBA support in the 2008 suite was really a pain in the neck.
Many thanks to the members of this forum!!

Regards,

Antonino

shg,

I have found where was the problem. In the code you provided me with, we have this:

rInp.Sort Key1:=rInp(1, ColUse), Order1:=xlDescending, _
Key2:=rInp(1, ColNum), Order1:=xlAscending, _

Now, when this macro is opened in Excel for PC, the second 'Order1' is converted into 'Order2'. With the Excel mac version, this is not corrected. Once manually corrected, everything works smoothly.

I must admit that I do not understand half of the code

Do you think that the macro could highlight all possible combinations (When 2 fluorochromes, not used otherwise by the other antibbodies, they would be highlighted)?

Once again, thank you so much for your help!

Antonino

Originally Posted by shg

Sorry, that was just a flat-out error.

I don't understand that.

EDIT: Or maybe I do. If you mean show the results for any possible combination of antibodies, that's 2^17 ~ 130K combinations. Just listing them would take 1M+ rows.

With an example, what I mean might be clearer:

Ab 1: Available with PE / FITC / PerCp /PE-Cy5 fluorochromes
Ab 2: Available with PE
Ab 3: Available with FITC

So, here, Ab2 must be PE; Ab3 must be FITC. Now, we have 2 possibilities left for Ab1: PerCp and PE-Cy5: Could these 2 fluorochromes be highlighted? (the user knows that it can use either)

Thank you!

Originally Posted by shg

There could be several possibilities of dye combinations for a given combination of antibodies, but only the simplest would admit to that process of highlighting alternatives on a single row.

The code will test all possible combinations if necessary, but quits when it gets to the first one that works. What's the point of listing additional possibilities?

Well, all fluorochromes are not strictly equivalent. For instance, one would prefer to use PE for certain antigens expressed at low levels. For antigens abundantly expressed, we can use other 'weak' fluorochromes. Having the panel of possibilities presented can definitively facilitate the choice...

Originally Posted by shg

Here's a hack.

My computer is off (midnight for me) but I will look at it as soon as I wake up...

Thank you again for all your efforts!!!!

Here I am.

shg, this is amazing! You did is exactly what I needed!! I don't know how to thank you?! Please if you have the chance to come to Paris, I will treat you at least a beer. I have been on this problem for so long, I just cannot believe that you solved it so quickly. You rock!

Very best regards!!