Excel / Google sheet formula. Help me before I lose it completely!

[JoanThursday]

Formula aces, I need your help!

I have a spreadsheet with a bunch of names in a particular column (lets call it A1). Some cells in this column have just one name (eg Brown, Bert (Mr)), where as others have several names (eg Green, Sam (Mrs), White, Ned (Mr), Black, Mary (Prof)). Layout of names in the cells is as shown in my examples.

I need to search the data in column A1 and find all the surnames that match the ones I have on a separate list. I'd like the formula to find instances of the names and put 'true' in a second column. If it find two names on the list in one particular cell, it needs only return 'true' once.

I tried to use =IF(OR(A1="Brown",A1="Green",A1="White"),"TRUE","FALSE") but it returned 'FALSE' each time, and it's obviously not correct. I can see the names are there!

I've also tried 'if ... then' and 'find' formulas but I'm getting myself in a right knot with the syntax.

Can anyone help before I throw the whole lot out the window?!

Got it! You just need to add all your names in the OR statement. I only did two.

@JoanThursday

Yes, I found that page *@LefttoherownDevizes*. I've bookmarked it because it's so useful!

Buuuuut .... I have 15 names to check against each cell. It's going to be a bloody complicated nested statement.

@IsolaPribby - exactly what I'm going to do!

I'm just lazy and world out search terms in one column, if statement in another and would concatenate to build the string

Did you try the link that LeroyJ posted? There was a follow-on link for searching for multiple words in a table.

@cornflakegirl, yes I saw that but just couldn't get it to work.

So, have gone back to basics: have extracted all the names out of single cells into their own columns, added another column for status (ie True, False and unknown) after each name, and finally applied conditional formatting.

The problem i have now is that I can't easily apply a pivot table, because there are just too many permutations: a simple table just won't work. Imagine an event with anything between 1 and 6 people attached to it, and each of those people has one of three statuses attached to them ... which is different to the others in their 'group'.

Anyway, with the conditional formatting and a couple of filters, it should get the message across.

Time for wine

Goodness, I had the most bizarre dream about this thread!
Random people were being affected by a strange ray from aliens in space. Scientists were trying to find out why. It turned out that it was all people who had ZH in their names. It was reported that the breakthrough was made by a woman working on a spreadsheet
In the dream I came back to the thread to ask the OP if she had been working on this, and it was confirmed! I even remember telling friends my involvement, and how much I was smiling!
I struggled to get back to sleep after a loo trip at 2am, and then had lots of dreams, which I forgot as soon as I woke up. But this one came right back to me as soon as I saw this thread this morning!
So, @JoanThursday, are you working on alien rays? 🤔

Haha - busted! 🤣

It's all down to nested formulas and pivot tables, dontcha know. They're saving the world.

The basic problem here is that the data is stored in a really useless way. We need to teach people how to store data in spreadsheets in ways that make subsequent analysis easy. The format you have it in would need some data wrangling to put it into a sensible format. Excel is not a good tool for data wrangling. VBA (the programming language built into Excel) can help a lot here, but getting the data in the right format in your organisation tackles the problem at source.

vita.had.co.nz › papers › tidy-dataPDF

Personally, I use R, which has great data wrangling as well as superb data analysis tools, but that's not going to be everyone's favourite.