Hi everyone, new user here. I'm an academic and am dabbling in a new project. I'm trying to measure the diversity of social networks in a part of the world where ethnicity can be identified by surname. I'm using publicly available data from Facebook. I've been doing this for a while using a cumbersome method, but I suspect that there may be a simpler solution using excel. Unfortunately, I haven't found an approach that works yet, hence the post here. Let me briefly explain what I'm trying to do.
I have one spreadsheet for each study subject. The country I'm doing this work in has three main ethnic groups. In column A, I have the 100+ most common surnames from ethnic group A. In column B, I have the 100+ most common surnames from ethnic group B, and in column C, the 100+ most common surnames from ethnic group C.
In column D, I paste the names of the subject's social network (in other words, everyone on their friend list). What I want to do is classify the ethnicity of each friend. To minimize manual work, the first cut would be to determine whether the names in column D appear in columns A, B, or C.
In short, I need a formula that will mark a "1" (or something similar) when a name in column D appears in column A (I'll use the same formula to check for matches with columns B and C), and "0" when it doesn't. The slight complicating factor is that columns A, B, and C have only surnames, while D will have surnames, given names, and nicknames. So the formula should code a match if one of the names in column D is match with A, B, or C (in other words, the cell contents will never be a complete match, because column D will never have only surnames).
I'd really appreciate any help on this. I've spent quite a while looking for solutions, but nothing has worked reliably. Thanks very much in advance!
Bookmarks