VLookups with Multiple Results

[rjbinney]

I'm trying to find a more efficient way of running a couple of linked tables. For right now, using Excel (as opposed to Access) is a MUST.

I have a list on a worksheet of about 1300 employees. Among other information, there is a column for "Training". Let's call this sheet "Master".

I have another worksheet with a list of 850 employees. Let's call this worksheet "Specials".

About half the employees on Specials are also on Master.

All employees on the Master sheet have attended either [L]eadership training or [C]apability development. SOME have attended BOTH. Each record of training is a new row on Master. So you may have:
- Mick, L
- Keith, C
- Charlie, L
- Charlie, C


I need to indicate on the Specials sheet which training an employee attended, according to "Master". Since some have attended both, I need to determine that.

What I'd like to happen is:

On "Specials", have one cell on each row that is blank if the employee does NOT appear in "Master"; return "L" if (s)he attended Leadership ONLY, return "C" if (s)he attended Capability Development ONLY, and "CL" if BOTH.

I've tried the array/index/match options, but that doesn't lay out quite right.

For a variety of reasons, I'd like to keep "Master" consolidated, and not create a "Master Leadership" and "Master Capability", although I know I could make that work.

Thanks.

 

[ango858]

just a suggestion, why dont you add two columns, one can display if they attended one event and return lets say a value of 1, 0 if they did not, the other column is for the other event with the same results. then make a third column that says if the first column and the second column =2 then "both" ,etc

 

[rjbinney]

Right, and use SUMPRODUCT.

Thought of that, but for one hitch....

SOME folks have attended the same class more than once. In the report I don't care how many times - I just need to know if they attended ONCE. But I'm stuck on how to represent that so 2 L's don't make a C.

 

[energman58]

How about sumproduct in each of your options and then test if that is >0 in your test for both so 2 Ls returns a 2 but this stil returns TRUE so something like

Cliff L
Cliff L
Tony C
Tony L

Using Sumproduct(--(A1:A4=Name),--(B1:B4="L")) returns 2 for Cliff and 1 for Tony so if you test this as >0 then it returns TRUE for both Cliff and Tony then you can extend teh formula to test for C in the same way and use an IF to determine if you return CL C or L (or nothing)

 

[rjbinney]

OK, yeah, I'll give that a try.

I don't understand the leading "--" in "(--(A1..." What does that accomplish?

Thanks!

 

[energman58]

-- forces the conversion of the boolean TRUE FALSE into numerics. Excel is slightly uneven in how it treats TRUE FALSE (as text or numeric text or numeric) but the arithmetic operator forces it to convert TRUE and False to 1 and 0 respectively.

Any mathematical operator should do the same job (like *1) but I'm not convinced that it would work with arrays (like in SUMPRODUCT) - anyway I was taught to use the "--" convention as it is both a sure fire way of enforcing the conversion and clearly shows that you are doing the "type conversion" so when you get back to the formula years later you can see what is happening - conversion rather some random mathematical operation.

I always do it although things may have moved on and it may be uncecessary in more recent versions of Excel - I have no idea - but better safe than sorry.

HTH

 

[rjbinney]

So if I understand correctly, I could mis-format my cell, and when the formula returns "True" or "False", it MAY count as TEXT"Zero" or TEXT"One" instead of number 1 or number 2... But if I put in "--", it will ALWAYS return a number.

Is that close?

 

[PCRIDE]

I would venture to guess you could also you If statements to check if they exist on one sheet or the other?

=if(AND(vlookup, Name, Master!,column#,false)=L,if(vlookup,name,Specials!,column#, false)=C)),"L/C",if(vlookup, Name, Master!,column#,false=L,if(vlookup, Name, Master!,column#,false=C),"L","C"))

Something like that.. I didn't test it

 

[energman58]

Yes pretty close

The -- will always turn an expression that returns TRUE/FALSE into numeric 1 and 0. So ISNUMBER("One") returns FALSE but --ISNUMBER("One") returns 0.

It will also turn misformatted numbers as text into proper numbers so if you have a formula which returns the value "1000" (the "word" rather than the number 1000) or if you format a cell with 1000 in it as text which means it gets treated as text even though the value in the cell is a number in eitehr case if you multiply it by 1 you get 1000 (it gets treated as the number automatically) but if you use some functions to refer to it like AVERAGE it will be treated as a text value (which are ignored for AVERAGE) unless you do the -- thing to force it to be treated as numeric.

Clear as mud eh?

 

[rjbinney]

Clear as mud eh?

Oh. Um, yeah. Sure. I'll just slllloowwwlyyy back away and stand over here...

Hey - the problem I have with VLOOKUPS (which is why I'd rather move to Access, but that's a whole different story), is when I have a few in a couple of sheets, particularly nested like that, it takes for-freakin'-ever to recalc. And this is supposed to be a neat clean worksheet.

(btw haven't had time to play with the solutions yet, will this weekend)

Thanks for all y'alls help.