How to calculate matching values in two cells to see percentage of accuracy?

[eric64418]

I have a list of CDs and LPs with UPCs that I found matching release pages for on the site Discogs.com - but I want to double check my work.

My Sheet has the UPC, Title, Release ID, and Description. I want to see the % of accuracy between Columns B & D are similar - knowing that some have some errant text such as (180g) or something - i would expect a rate of 80-90% means I am correct, but a percentage lesser than that needs investigation.

I have around 11k of these codes, so doing this manually would be quite a pain.

Any help would be MOST appreciated.

Thank you!!
Eric

 

[JW00]

Hi, this isn't what you asked for but it may solve your problem, judging by the image the data in column B seems to follow a pattern, the Artist, Title, Additional Info, if this is the case my approach would be to remove the Artist & Additional Info & check for a match with column D, the following formula will do that.

=IF(D2=(IF(AND(LEN(B2)>LEN(SUBSTITUTE(B2,"- ","")),LEN(B2)>LEN(SUBSTITUTE(B2,"(",""))),TRIM(MID(B2,FIND("- ",B2)+1,FIND("(",B2)-FIND("- ",B2)-2)),IF(LEN(B2)>LEN(SUBSTITUTE(B2,"- ","")),TRIM(RIGHT(B2,LEN(B2)-FIND("- ",B2))),IF(LEN(B2)>LEN(SUBSTITUTE(B2,"(","")),TRIM(LEFT(B2,FIND("(",B2)-1)),B2)))),"Match","No Match")

 

[eric64418]

You are a wizard! This is very helpful, thank you so much!

I do have a question though - I ran this code and for titles that are exact matches (no extra words) it generates "No Match".

 

[JW00]

If possible can you post some actual data, there maybe some additional hidden characters, when I type the data as shown in the picture the formula seems ok.

 

[eric64418]

It seems for some 45 RPMs, when the title has a / between the two songs, that causes an error. That might be it Plus, row 89 in this screenshot should be able to match.

 

[JW00]

Sorry I can't tell from the picture, some spaces maybe characters like 160 (Non-Breaking space), if you could copy a section of your workbook to a new workbook & upload it to Free Hosting Site & post the link, I will download it & try to sort the issue out.

 

[eric64418]

@JW00 -- here is the link: https://file.io/LaQV3nesvMIf

Thank you so much for looking into this for me

 

[Fluff]

That link comes up as dangerous with the message

Website blocked due to phishing​

I would suggest that you upload to a more reputable site, such as OneDrive, GoogleDrive, DropBox.

 

[JW00]

Sorry I didn't know, I hope this is a Google Drive Shared Folder Link, if you upload it I will take a look in the morning, thanks.

 

[eric64418]

Here's a google drive link: Loading Google Sheets