Cells with multiple values

[Ipsl]

Can I make a cell read as if with an OR?

I have a list of combinations and some of them can be expressed differently. Is there a way to add the variations to the same cell for VLOOKUP to read them? Heres my file Im referring to sheet two around O193

[Ipsl]

Is this possable or could I create a formula that hides all the cells that are <0 than something else? Im not too sure on this one.

[jevni1974]

Hi there,

I had a look at the file you posted, but I am not sure what you are trying to achieve. Can you explain more clearly using an example?

[Ipsl]

Hi

So if I want type one of the letter squences in say cell say A13 I'm going to use VLOOKUP in cell B13 to say the name of the sequence. Those are all found on the second sheet. Now on the second sheet you can see that Ive been making the combination variations.

ex: Ab pp AA = bA pp AA there the same value, genetically speaking so I want to put the variations in the cells next to names, so I dont have to copy and paste and pull down the list.

[jevni1974]

So basically you need an automated mechanism that takes the combination you entered eg bA pp AA and based on that code tries to find the corresponding description in the list in sheet 2.

If it cannot find a matching description instead code Ab pp AA is used to lookup the description. Until it finds the matching description (listed with one of the genetical synonyms)

So when AA AA AA is entered, this is the only synonym there is.
but when Ab Ap Ae is entered, there would be 8 synonyms.
This means that ideally you would need 1 lookup and in the worse case you would need 8 lookups before you find the description.

the easiest way in my opinion is to write an custom lookup function (macro) that works the same as VLookup, but if it cannot find the description it tries another combination and keeps on doing that until all combinations are tried, or a description is found. So the function itself generates the next combination.
This way you only need to record one code per description

Would you like me to write the function for you? or can you do that yourself?

[Ipsl]

I have no Idea hw to do that.. Im going to be applying this to a much larger range. Though if you make me on I can play with it and apply it. I just learned today the the $ sign means allways. Thanks in advance bro.

[jevni1974]

Hi There,

Attached you find an updated version of your posted excel file.
I included a module with the function altVLookup which I wrote to tackle your problem.

In sheet one I put some sample lookups so you can see that it works
if you press ALT F11 or select Tools -> Macros -> Visual Basic Editor, you can view the code I included for altVLookup (Module1)

If you have any questions... just let me know

Hope it helps

[Ipsl]

Hey there so it identified the first example but than all the rest read #N/A. What did I do do wrong or what am I supposed to do now?

[jevni1974]

hmm... sounds strange to me... and its ### annoying as it is working perfectly in my own excel (see attached check.jpg)

did you change anything to the code? or did you just open the sheet?

Can you open your Visual basicv editor (ALT F11) then select Tools -> References. can you check whetehr they match with the references in the screendump I attached (references.jpg)

If they don't match you need to update your references accordingly.

Let me know if that does the trick....

[Ipsl]

I just opened it said enable macro? and I did. and this is what I got. The references match exactly.
http://s182.photobucket.com/albums/x...urrent=off.jpg

it wouldnt upload so I had to put it in pbucket

[jevni1974]

Well yes thats indeed proof that it doies not work yet in your excel...
Can you check the library references like I mentioned in my previous post?

[Ipsl]

opps Sorry I thought it was on that..
http://s182.photobucket.com/albums/x...rent=off-1.jpg

there ya go. Thnaks alot by the way your helping more than you know. And on another note. When Im dealing with 8 sets of letters, I would add

Please Login or Register  to view this content.

But change strSec3a & strSec3b to strSec4a & strSec4b

correct? O I just noticed cause Im on v 07 that it says office 12.0 insted of 11.0. Could that be the issue?

[jevni1974]

Hi,

I see you are using Excel 2007, I am using 2003 still.
I don't know if that is causing any problems...don't think so...

When you move your mouse pointer over the green triangle in one of the cells that has has value N/A, what does the error tell you?

Regarding the logic, its not that simple.
I guess you mean that you want to update the logic to handle 4 pairs of 2 chars instead of 3 pairs. Am I right?

If so, you need to make sure that it can handle the 16 possible combinations
If you look in the comments I wrote you need to extend the list with another 8 combinations (2^4=16)

can you zip and post the file with the N/As, and I will change the function to handle combinations of 4 pairs of characters

[Ipsl]

There ya go and Im actually going to be dealing with 8 pairs ultimately... So If you just wanna add for 4 pairs or more I'll try and figure it out.

[jevni1974]

Okay,

I downloaded the file you posted and I seem to be facing the same issue as you are...
its a little coding mistake I made.

the following code:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Then it works fine. I posted the updated file again.

additionally I will elaborate the logic up to 2^8 = 256 combinations.
I will post that one within a couple of days...

[Ipsl]

There we go thats works now. Thanks so much bro. Just so you dont think Im no doing anything I finised the workbook that contains all 256+ 6000+ codes in this one as well. Should I make a list of all the appropriate names while your doing that?

[jevni1974]

Good to hear,

So it indeed does what it needs to do?

Yes you need to make a list with one combination of characters ( so one synonym) and its corresponding description.
Thats all whats needed. you dont need to add all the synonyms anympore as the alternative VLookup is taking care of generating synonyms while search for a description :D

[jevni1974]

Hi,

I have updated the function in such way that it can handle any combination of character pairs. So it should be able to handle your current 3-pair combinations list, as well as the 8-pair combinations list you are creating.

I hope it works well.

If you have any questions, please let me know.