formula for classifying data from facebook

**kaiost** · 09-17-2013, 02:36 PM

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!

**FDibbins** · 09-17-2013, 02:40 PM

Hi and welcome to the forum

if we can isolate the last name, this should be fairly easy. can you provide a sample list of names from all 4 columns for us to work with?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**kaiost** · 09-17-2013, 02:57 PM

Thanks for the quick reply. A sample file is attached.

D1 should be a column A match
D2 a column C match
D3 no match
D4 a column B match
D5 a column A match

Unfortunately it's not possible to isolate the surname is column C; because of the structure of names, it can come first or last.

Thanks again!