Name check / data validation formula

@Doofus

Do NOT open any more threads on this. You have now asked essentially the same question in three threads. If you have any further questions on checking and matching names between columns, ask them HERE.

I will close any further threads I find on this issue. These questions are not different enough to warrant separate threads. I hope I have made myself clear.

Hi AliGW,
Sorry about the mass confusion about the multiple threads.
To you, they are all the same and simple. To me, they are bigger problems than anything Einstein ever thought of.

So, I have questions about a formula you provided - in the prior /closed thread:

=IF(ISNUMBER(FIND(MID(B3,FIND(" ",B3)+1,99)&", "&LEFT(B3,FIND(" ",B3)-1),C3)),"OK","NO")

What is the purpose of the +1, -1 and the 99 ?

I think i get it that the formula is matching the data in the cells.
But what is the purpose of those digits?
Sorry about the dumb question.

Also, if anyone else wishes to add to my education on an explanation of what the formula is meant to do, feel free to add.
Thank you
Doofus

Hi Folks,
I have another related question.
This is about extracting a name that always appears between certain words and abbreviations/symbols etc.
I have uploaded an Excel file to show how I want to extract the information.
I've looked at some Youtube videos - but they seem to be about extracting names from e-mail addresses and so, not much use.
In my issue, the names to be extracted will always appear after the words "written by" and before "(RE: related document(s)"
I have about 200,000 lines of data from which to extract the name.
Does anyone know of a formula that use to enter in the first cell and copy down - to extract the data?
Thank you all
Doofus

Or if anyone knows of a Youtube video that answers the question, please provide a link - if it is acceptable per whatever forum rules are not violated.

Doofus it appears that you mistakenly gave The_Snook a neg rep, instead of a pos rep - I have rectified that for you

Hi FDibbins
Thank you!!
My apologies to The_Snook.
He/She helped me immensely.
Please note that my day isn't complete unless I do something, dumb , that lives up to my moniker..
Thank you
Doofus

Hi, The_Snook,
My apologies. I guess I hit the wrong button - I got excited with your answer/solution
YOU HELPED ME IMMENSLEY.
I hope you can forgive my oversight.
Best regards
Doofus

=IF(ISNUMBER(FIND(MID(B3,FIND(" ",B3)+1,99)&", "&LEFT(B3,FIND(" ",B3)-1),C3)),"OK","NO")

The +1 tells Excel to find the letter after the first space in B3. -1 means the letter before the space. 99 is the number of letters after the found letter to return.

I suggest you use the Evaluate Formula function on the Formulas ribbon to step through the formula to understand what it is doing.

For your latest query on a recurring theme, try this:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("written by",A2)+11,99),"(RE:",REPT(" ",99)),98))

You're welcome.

As I said, take the time to run each of these name extraction formulae through the Evaluate Formula feature - it will begin to help demistify the mechanics for you. You might even be able to start building your own versions instead of coming straight back here asking for the nexat adaptation.

Yes, there is an issue with yours: it leaves you with a leading space at the beginning of the text string it returns. Study mine to see what you could add to make that leading space go away.

You should be aware that, as a retired teacher, I do like to see people learning and beginning to apply what is learnt.

This is why you should keep all these very similar requests together: it's easier for those helping you to see what you have already been told and to help you adapt and build on the tricks we share with you.

Hi Folks,
And now, I have another question related to previous issues.
I have uploaded an excel spreadsheet with the issue.

In cell B3, I have typed my formula =MID(A3,FIND("written by",A3)+11,FIND("(RE:",A3)-FIND("written by",A3)-11)
to extract a name from text in cell A3.

However, in cells A4,A5 and A6 there is no data that matches that criteria and so, I'd like to skip the cells associated with formula in cells B4,B5 and B6 and move on to cell B7, B11, B15 etc

I could use an IFERROR formula and type =IFERROR(MID(A3,FIND("written by",A3)+11,FIND("(RE:",A3)-FIND("written by",A3)-11),"")
and make the #VALUE! message disappear.

However, is there a way or is there a formula that can skip the unwanted cells and move, automatically, to the required/designated cells ? So that, when I copy down and paste, it misses/disregards those cells.
The data files have about 400 to 600,000 such lines - I have uploaded a small mock up of the relevant part of the file.

All/any input is welcome.
Thank you
Doofus

No - IFERROR is the correct approach.

To apply a formula only to certain rows would require VBA.

What should happen if the next one is also empty? Or will there only ever be one at a time? Will the numbers be in column A, or is this for illustration purposes? What will be in the rows between?

This is not a complete sample: I think you have over-simplified it, so any suggestions will most likely fail on the real data.

I won't look at this until you've provided much more realistic sample data - sorry.

In C4 copied down:

=IF(LEFT(B4,10)="Written by",B4,IF(MOD(ROWS(B$4:B4),5)=1,"Missing data / "&LOOKUP(2,1/(C$1:C3<>""),C$1:C3),""))

EDIT: This may NOT work because of this:

In the rows between there is other data eg year, significant works by authors, movie adaptations etc

Come on, Doofus - you should at least know by now that we need REALISTIC sample data, not lazy examples. This is truly exasperating, especially as you expect US to do most of the leg work for you. I'm not in the mood to waste time today, so please put together a REALISTIC data sample and I will have another look. If you don't, then good luck to you!!!

Well, at least tell me if my solution works on your real data.

Where in the world are you?

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Hi Folks,
I have another question about matching data.
I have uploaded an Excel file. Please look at SHEET 3.
I am trying to match the data in all three cells so that I get a"YES" or "NO" answer in cell column D based, ideally, on the last name.

My idiot formula =IF(AND(A2=B2=C2),"OK","NO") does not work.

Can someone show me a formula or formulas that works for this type of issue?

*The text (CR) and (MA) do not matter.

The names will always appear in this format - and there are thousands of data rows to check.

I cannot figure out how to write the formula because on column C, it is last name first.

I need to copy the formula down, so that I can get the answer and then filter to check - so that I can have them corrected.
Best
Doofus

SECURI will not let me post the VBA code !!!!

I cannot figure out how to write the formula because on column C, it is last name first.

You should do. It was dealt with in I think the first if your three threads on this stuff. I gave you the formula for comparing names in different formats. It's clear from this latest request that you haven't even attempted to use it or any if the several variants you've been given.

Hi AliGW,
Tried using = IF(ISNUMBER(FIND(MID(A3,FIND(" ",A3)+1,99)&", "&LEFT(A3,FIND(" ",A3)-1),B3)),"OK","NO")
to search for A and B.
and I was hoping to use it to check for B and C .
That would have created two "Yes" "NO" columns.
But I have got messed up.
Hence request for assistance.
Thank you
Doofus

OK. Here's another variation on a theme:

=IF(AND(A2=B2,SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,99)&", "&LEFT(A2,FIND(" ",A2)-1),"(MA) ","")," (CR)","")=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," (MA)","")," (CR)",""),"(",""),")","")),"OK","NO")

Slight mod to Ali's formula: added TRIM

=IF(AND(A2=B2,TRIM(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,99)&", "&LEFT(A2,FIND(" ",A2)-1),"(MA) ","")," (CR)",""))=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," (MA)","")," (CR)",""),"(",""),")",""))),"YES","NO")

Good call, John. Amended WB attached.

OK - I did the leg work, but never mind!

Please mark this as solved.

I don't think you understood the comment. Read posts #37 to #39 again and you may understand.

However, I am not the greatest Excel logician - nor do I have much time to think things through. ...
I know what I want to achieve but lack the technical skill to achieve it - which is why I come to this Forum.

Which is fine, however you MUST remember that this is a HELP forum, not a DO IT ALL FOR YOU forum. We should be helping to steer YOU to a solution, rather than constantly providing solutions for you. You are beginning to stretch my patience in this respect.

If you don't want to get your hands dirty (and suggesting that you had tried =IF(AND(A2=B2=C2),"OK","NO") in the context of everything that had gone before tells me very clearly that you don't), then perhaps you should consider purchasing points in the Commercial Services section where someone will happily do the work for you to your brief.

Just sayin' ...

Hi Mr Topley,
I tried your formula on one set of data - (for series characters). It worked like a charm.
I then tried it on a different list - for data entry personnel.

=IF(AND(A2=B2,TRIM(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,99)&", "&LEFT(A2,FIND(" ",A2)-1),"(SA) ","")," (TR)",""))=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," (SA)","")," (TR)",""),"(",""),")",""))),"YES","NO")

The only adjustments I made were for (SA) and (TR)

It does not produce the required answer.

I have uploaded an Excel sheet.
Column D has the formula and column E shows the desired result.
Could you look at the formula and check to see what I am doing wrong?
Also, anyone else who is willing to review the sheet is welcome to add their input.
Thank you
Doofus

You have swapped columns B and C around and in some rows you have different values in all three columns.

My formula was built on the basis of the previous dataset, where in all cases A and B were the same. The goalposts have shifted - again.

I'm out.

As per Ali's reply: if you change the goalposts you cannot expect Excel to accommodate this automatically so yet again you have wasted contributors time.

If you want Ali's formula ( - she did all the hard work so that is where the credit belongs - ) to work then first swap the columns B & C as per the original post.

Further, again noted by Ali, the data has also changed e.g. Jenny Silberman (TR) (Silberman (TR), Jenny) Jenny K Silberman (TR) - so your sample was not very representative. Comparing data such as this is not as easy as it might appear to the human eye. I have not tried to see the impact on the formula but suspect it is not trivial.

Even the VBA solution I provided will also need changes: I appreciate VBA is not always allowed.

And why are these differences occurring? - "Prevention is better than cure"

For Excel2013+
Maybe try

=IF(ROWS(FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&" "&B2&" "&C2,"(",),")",),",",)," ","</m><m>")&"</m></x>","//m[string-length(.)>2][not(preceding::*=.)]"))=2,"YES","NO")

I did start working on this, but Bo_Ry has provided a better alternative. I won't be offering any further help on this - sorry.

The target/goal posts keep moving because of things I discover in the data - as I sift through them - it's pretty thankless at my end too.

Take a step back: do ALL of your data sifting first, then present sample data that covers ALL eventualities. It simply is NOT fair on those giving generously of their free time to help you to keep expecting them to reinvent the wheel because you have spotted yet another anomaly. Your helpers' good will only gets you so far: you can begin to abuse it if you are not careful.