Formula to find a word in the body of text in a cell...

[dkballs]

Hi, I am trying to create a spreadsheet where it will compare a value in one cell against the value in another. Seems simple and would be if it was an exact match, but its not. So column "A" has the body of an email that has been exported from outlook into excel. Contained within the text will be a patient number. In column "C" will be the patient number that I need to find within the text body of column "A". I then need it to return a value in column "D" that tells me which row has the patient in.

For context there would be around 800 emails in column A and then column C will have about 500 patients. Basically the sheet is intended to see if an email referral has been received against a list of patients that should have been referred. Currently this process is done manually by a clerk! line by line.

So, here is an example of my spreadsheet thus far:

Ideally column D would return a value that tells me which cell the patient data is in. So D2 would say A2 (as there is the exact patient number there. D3 would also return A2 as the patient was referred in on the same email. D4 would return A4 and D5 would return A4.

I have tried a VLookUp with a wildcard but this don't work:

=VLOOKUP(C2&"*",A:A,1,FALSE)

I think I'm stuck! Any help would be gratefully received.

 

[Yongle]

If there are a lot of duplicates you may prefer a solution that gets you there in one step ... which is why @Peter_SSs is asking you for more information including your Excel version (hint hint !!)

But if there are relatively few duplicate entries ....
... you could simply use another column to identify duplicate entries and then use Excel's standard search tool to list the duplicates

Formula in E2 copied down identifies duplicates
=IF(COUNTIF(A:A,"*"&C2&"*")>1,"Dup","")

and then to use the search tool like this ...
highlight column A
click on Home tab \ Find & Select \ Find \ enter the number in the box (wildcards are not required) \ Find All

OR
... use you could conditional formatting rather than Find tool for the second part
- let me know if you are interested in pursuing that

 

[dkballs]

What version of Excel are you using? (Suggest updating your profile so helpers will know what version you are using)

The work computers are using Excel 2010

 

[dkballs]

Thanks Yongle. The duplicate works well.

Funnily enough the method you mention is how they manually validate now (Ctrl+F) the clerk compares the 2 lists manually but enters the 400+ patient numbers in one by one! - thus this solution.

With regards the previous post, If column D returns 1 entry. How can i get column F to show copy/paste the corresponding cell?

So F2 would look at the value of D2 and then copy the corresponding cell (A2) into F2... (Hope that makes sense?)

The rationale being that A: and C: are the input. D:, E: and F: become the output that allow the clerk to then ensure the patient referral has been emailed correctly. I am sorry that the data provided is a bit cryptic but the email content is significantly more detailed than that shown and the patient numbers are also made up in this sheet (I can't post real email or patient numbers).

 

[dkballs]

Are you sure?
Your patient numbers seem to vary in length, so that formula may well return incorrect results as shown below

Book1
	A	B	C	D
1
2	abc 98712355 def 456 ghi		123	A2
Locate Patient

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ADDRESS(MATCH("*"&C2&"*",A:A,0),1,4),"")

Give us some data in a form that we can copy to test with. My signature block below has help with that.

Apologies the admin rights on the work PCs don't allow for addins or I would add the MrExcel add in.

 

[Peter_SSs]

Apologies the admin rights on the work PCs don't allow for addins or I would add the MrExcel add in.

In that case can you either ..

• Upload a small sample file with dummy data where my formula is not working and provide a link here, or (not quite as good but...)
• Simply copy and paste a small section of your worksheet here (like below)

,, so I can try to determine why my original formula does not work with your data.

abc 98712355 def 456 ghi		123​
xy 987		456​
qwerty 555 and 666		789​
		666​

 

[Yongle]

With regards the previous post, If column D returns 1 entry. How can i get column F to show copy/paste the corresponding cell?

This returns column A value for those entries in column C that are only found ONCE in column A
=IF(COUNTIF(A:A,"*"&C2&"*")=1,INDEX(A:A,MATCH("*"&C2&"*",A:A,0)),"")

 

[dkballs]

Yongle - Amazing thanks! This will save so much time (you have no idea!)

Thank you both for your support.

Peter - I cant work out how to upload the spreadsheet? sounds stupid but every Cut/paste I do results in the forum creating a thumbnail and the only attachment option seems to be "upload image". I cant see how to post the sheet without the addin.

 

[Peter_SSs]

What about the issue of different length patient numbers I raised in post 8? You didn't comment on that but it seems to me that could lead to errors with the suggested formula.

For example, with my sample data below, the suggested formula (column D) returns incorrect results
In D2, no result is returned even though that patient number does only appear once (in A3)
In D3 data is returned for a different patient number to the one listed in C3. Also, that data returned in D3 is repeated again (correctly this time) in D7.
Further, there is no distinction between a patient number that occurs multiple times and one that does not appear at all (see C4:D5).

The alternative in column E addresses those circumstances. I also prefer not to use whole column references as for some functions it will slow the sheet calculation noticeably. I have used down to row 1000 and you can change that to whatever you like so long as it covers any number of rows you are likely to actually have in your worksheet.

Book1
	A	B	C	D	E
1
2	Patient data for 852727272727		2727272727		Patient data for 2727272727
3	Patient data for 2727272727		8527272727	Patient data for 852727272727	Not Found
4	Patient data for 5656565656		5656565656		Dupe
5	Patient data for 7777777777 and 5656565656		9875623489		Not Found
6			7777777777	Patient data for 7777777777 and 5656565656	Patient data for 7777777777 and 5656565656
7			852727272727	Patient data for 852727272727	Patient data for 852727272727
8
Locate Patient (2)

Cell Formulas
Range	Formula
D2:D8	D2	=IF(COUNTIF(A:A,"*"&C2&"*")=1,INDEX(A:A,MATCH("*"&C2&"*",A:A,0)),"")
E2:E8	E2	=IF(C2="","",CHOOSE(MIN(COUNT(SEARCH(" "&C2&" "," "&A$2:A$1000&" ")),2)+1,"Not Found",INDEX(A$2:A$1000,MATCH("* "&C2&" *"," "&A$2:A$1000&" ",0)),"Dupe"))

 

[Yongle]

What about the issue of different length patient numbers I raised in post 8?

@dkballs - the concern raised about numbers of different lengths is valid
- is the data you supplied truly typical ?
- there appears to be a space both before and after each number in column A
- is that is always the case?
- inserting spaces in the same positions in the search string (as illustrated below) would deal with that concern

Previous formula to identify duplicates
=IF(COUNTIF(A:A,"*"&C2&"*")>1,"Dup","")
amended
=IF(COUNTIF(A:A,"* "&C2&" *")>1,"Dup","")

Previous formula returning column A value for those entries in column C that are only found ONCE in column A
=IF(COUNTIF(A:A,"*"&C2&"*")=1,INDEX(A:A,MATCH("*"&C2&"*",A:A,0)),"")
amended
=IF(COUNTIF(A:A,"* "&C2&" *")=1,INDEX(A:A,MATCH("* "&C2&" *",A:A,0)),"")

Please examine your data and confirm that there is a space either side of every number
- it would be comforting to know that a patient's non-referral being missed was not due to a formula supplied by me

 

[dkballs]

Nothe dummy data I supplied isn’t typical. The number is consistent in length normally and would normally be 12 digits long. 123 456 789 123.

So what the workbook will do is look at emails that have been sent from the clerk over a given date period. It will then compare the referral list against the body of the sent email and this will be used to check that the referral has actually been sent on to the right department. It’s an added check that is currently completed manually but there is also the fail safe that the receiving department would chase the referral if it hadn’t arrived (so this spreadsheet is more a check of a check (as there cannot ever be a single point of failure)).

When a referral arrives it has the gaps in it as above. My plan was to import the referral list ahead of using this sheet into a separate worksheet. Apply a macro to snip all the numbers in the column so there is no gaps: 123456789123 and then copy the column from the “import” work sheet into the “comparison” works sheet (column C), which is the one you’ve helped me with the formula.

Outlook would then export the emails sent box into another import sheet where the data would be cut down into a given date. The subject body of the email (which is where the referral is referenced “see attached referral for patients 123456789123 and 246123246123 for your department today”) is then copy pasted via a Macro into column A.

The issues you’ve helped me overcome were that the numbers are contained within the body of a text and also sometimes one email will have multiple referral numbers in it as they’ve been forwarded on in a batch.

hope all that makes sense and reassures you that no patients slip through the gaps.

I’ll run the sheet for the first time tomorrow so there will be alot of scrutiny as we see if it will speed up the validation work.