Presence of values within a column when observing two rows

[drewsta]

Hi,

I have the following data:

Excel Data.JPG

I have two rows for Jim (a&b) and two rows for Bill (a&b). Each value associated to each name is recorded as a letter (either a,b,c,d). What I would like to do is for rows 6 and 7 to recognise as to whether a particular column has a letter value against it combining Jim a and Jim b; repeating this for Bill a and Bill b. For example, for Jim: Column B both Jim a and b score, so I would like the cell (in this case B6) to recognise that fact, perhaps give it a 'P' present; Column C for Jim (C6) would be blank as no letter is present for Jim a&b, whilst both column D & E (D6 & E6) should return 'P' as Jim a has a letter present (despite Jim b not having a letter present).

I apologise for long dogged explanation. But I hope someone can help.

[gutkinma]

Hi Drewsta,

Here is what I can suggest, let me know if it fits the purpose:

Formula in cells B6 to E7 and further.

=IF(SUMPRODUCT((--(ISNUMBER(SEARCH($A6,$A1:$A4)))),(--(B1:B4<>"")))>0,"P","")

It looks for work Jim in row 6 and for Bill in row 7, and basically for any name the you have in column A, to the left of your formula.

Then when it finds the name in the range above (A1:A4 in this example) [regardless of what the name is followed by - a letter or number or something else], it looks for what is in respective rows in the column of the formula, and if it is not empty, retunes a number (otherwise returns 0). If value is >0, returns 'P'.

(--) is used to convert TRUE/FALSE returned by formula into numeric value - 1/0 respectively.

Cheers,
Michael

[drewsta]

Hi Michael,

Many thanks for your quick response. That works great. Rather than restrict it searching particular cell ranges, how would I adapt the formula to look for associated combination of names and the numbers within those rows?

[gutkinma]

Could you please include a workbook with more specific example please?

[drewsta]

Hi Gutkinma,

Please refer to my data picture in the #1 post. I would like Cell B6 to lookup the data range B1:B4 by searching column A and identifying the relevant rows named Jim a or Jim b to identify the relevant associated cells in column B.

[gutkinma]

Hi Drewsta,

Please see attached workbook.

Hopefully this does the trick.

Thanks

Book1.xls

[drewsta]

Is there a way of doing the same by combining searching for two different words. So if I wanted to carry out the same function but instead of having Jim a & Jim b I had Jim and Sarah how should the formula be constructed?

Excel Data.JPG