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]

Well hopefully we got there in the end, there were no unusual characters in the strings, but the pictures didn't show enough detail about the strings. Initially I tried searching for the last word in string D & using that as a cut off for the Mid function in B & whilst that worked for the vast majority some records had repetitions like “(Extended Mix)” appearing twice in B & D which caused the formula to cut off at the first occurrence. In the end the answer was a much simpler formula, it now looks for the same initial start position after the Artist but then extracts the length of string D from that point & checks for a match with D. In the examples provided there is only one “No Match”, I have added column F (Extraction) just so you can physically see if they match, but you can just ignore it in your worksheet it is just for demonstration purposes.

Discogs.xlsb
	A	B	C	D	E	F
1	UPC	FullDescription	DiscogsReleaseID	Description2	Match	Extraction
2	54645665414	Guidance (ltd. ed.) (picture disc)	r15418244	Guidance	Match	Guidance
3	54645635516	Cutty Ranks - Grizzle / Cocoa Tea - Sonia (rhythm: "Punanny")	r505655	Grizzle / Cocoa Tea - Sonia	Match	Grizzle / Cocoa Tea - Sonia
4	54645634113	Cutty Ranks - Limb By Limb (Vocal); Cutty Ranks - Limb By Limb (Acapella) / Cutty Ranks - Limb By Limb (Hip Hop Mix) (rhythm: "Pitch")	r1913711	Limb By Limb (Vocal); Cutty Ranks - Limb By Limb (Acapella) / Cutty Ranks - Limb By Limb (Hip Hop Mix)	Match	Limb By Limb (Vocal); Cutty Ranks - Limb By Limb (Acapella) / Cutty Ranks - Limb By Limb (Hip Hop Mix)
5	54645635912	Dawn Penn - You Don't Love Me (No No No) / No No No Version (rhythm: "Dawn Penn - You Don't Love Me / AKA No No No")	r2811476	You Don't Love Me (No No No) / No No No Version	Match	You Don't Love Me (No No No) / No No No Version
6	54645636513	Devonte & Peter Metro - Say Wooee / Beenie Man - Bookshelf (rhythm: "Bookshelf")	r1664807	Say Wooee / Beenie Man - Bookshelf	Match	Say Wooee / Beenie Man - Bookshelf
7	54645637718	Foxy Brown - Sorry; Version 1 / Version 2	r902855	Sorry; Version 1 / Version 2	Match	Sorry; Version 1 / Version 2
8	54645652414	Gregory Isaacs - Mr. Know It All (Extended Mix) / Ossie Hibbert, Revolutionaries - War Of The Stars	r5609275	Mr. Know It All (Extended Mix) / Ossie Hibbert, Revolutionaries - War Of The Stars	Match	Mr. Know It All (Extended Mix) / Ossie Hibbert, Revolutionaries - War Of The Stars
9	54645637916	Gregory Isaacs - Rumours / Version (rhythm: "Telephone Love AKA Rumors")	r1584432	Rumours / Version	Match	Rumours / Version
10	54645636216	Ini Kamoze - Hot Stepper / Stepper Version	r656098	Hot Stepper / Stepper Version	Match	Hot Stepper / Stepper Version
11	54645653510	Itals - Ina Dis Ya Time / Yellowman - Operation Eradication; Harbour View Rock (Instrumental)	r7003747	Ina Dis Ya Time / Yellowman - Operation Eradication; Harbour View Rock (Instrumental)	Match	Ina Dis Ya Time / Yellowman - Operation Eradication; Harbour View Rock (Instrumental)
12	6.01811E+11	Johnny Osbourne - Fally Ranking (Extended Mix) / Johnny Osbourne - Trechtown School (Extended Mix)	r12108077	Fally Ranking (Extended Mix) / Johnny Osbourne - Trechtown School (Extended Mix)	Match	Fally Ranking (Extended Mix) / Johnny Osbourne - Trechtown School (Extended Mix)
13	54645637312	Lady G - Nuff Respect / Version (rhythm: "Telephone Love")	r1796465	Nuff Respect / Version	Match	Nuff Respect / Version
14	6.01811E+11	Michael Prophet - Gunman (Extended Mix) / Michael Prophet - Cassandra (Extended Mix) (rhythm: "Michael Prophet - Gunman")	r2334396	Gunman (Extended Mix) / Michael Prophet - Cassandra (Extended Mix)	Match	Gunman (Extended Mix) / Michael Prophet - Cassandra (Extended Mix)
15	54645634212	Michigan & Smiley - Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix) (rhythm: "Diseases AKA Golden Hen")	r1585354	Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix)	Match	Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix)
16	6.30283E+11	Reggae Regular - Where Is Jah (Extended Mix) / Reggae Regular - Black Star Liner (Extended Mix)	r14652106	Where Is Jah (Extended Mix) / Reggae Regular - Black Star Liner (Extended Mix)	Match	Where Is Jah (Extended Mix) / Reggae Regular - Black Star Liner (Extended Mix)
17	54645636117	Reggie Stepper - Kimbo King / Whining Skill (rhythm: "Stalag")	r2509059	Kimbo King / Whining Skill	Match	Kimbo King / Whining Skill
18	54645633611	Shabba Ranks - Wicked In Bed / Singing Melody - Groovy Kind Of Love	r1788202	Wicked In Bed / Singing Melody - Groovy Kind Of Love	Match	Wicked In Bed / Singing Melody - Groovy Kind Of Love
19	54645633512	Sister Nancy - Bam Bam / Stalag Version (rhythm: "Stalag")	r163058	Bam Bam / Stalag Version	Match	Bam Bam / Stalag Version
121	5.29101E+12	10000 Maniacs - Planned Obsolescence: Live '88 (remastered)	r9882267	Live At The Ritz NY 7th August '87	No Match	Planned Obsolescence: Live '88 (re
Sheet1

Cell Formulas
Range	Formula
E2:E19,E121	E2	=IF(D2=(IF(LEN(B2)>LEN(SUBSTITUTE(B2,"- ","")),TRIM(MID(B2,FIND("- ",B2)+1,LEN(D2)+1)),LEFT(B2,LEN(D2)))),"Match","No Match")
F2:F19,F121	F2	=IF(LEN(B2)>LEN(SUBSTITUTE(B2,"- ","")),TRIM(MID(B2,FIND("- ",B2)+1,LEN(D2)+1)),LEFT(B2,LEN(D2)))

 

[eric64418]

Thank you so much.... you've saved me a TON of time manually checking all of these. Really appreciate your efforts!!

 

[JW00]

No problem, all the best with your project.

 

[eric64418]

Hi, I had a bit of trouble - i left another file in the google drive folder. Could you take a look?

 

[JW00]

Hi, I have looked at the new sheet & whilst it is obvious that the columns relate to the same record (see below), there are no discernible patterns, the columns have different punctuation (line breaks etc..) & the basic configuration is too different to create a match, I will have a think & take a proper look sometime over the weekend.

Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix) Michigan & Smiley

Diseases / Boneman Connection Michigan & Smiley /

Nicodemus

 

[eric64418]

Hi, I have looked at the new sheet & whilst it is obvious that the columns relate to the same record (see below), there are no discernible patterns, the columns have different punctuation (line breaks etc..) & the basic configuration is too different to create a match, I will have a think & take a proper look sometime over the weekend.

Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix) Michigan & Smiley

Diseases / Boneman Connection Michigan & Smiley /

Nicodemus

Thank you so much for taking the time to think about this.... I can restructure either 'entry' - but still the things like (Extended Mix) will be in there... Yeah I'm not sure if this is possible in excel... definitely a tough one.

 

[Alex Blakenburg]

You might want to give the Microsoft Excel Add-In Fuzzy Lookup a try.
You can it from Microsoft here.
Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

Power Query also has Fuzzy Lookup but the Add-In shows the percentage match which you are after while the PQ version does not.

Below are
1) the first 20 lines of you sample file with the % match up.
2) unmatched lines (2)
I set the minum match allowed to 25% and return only 1 result for each line (the match with the largest %)
Excluding the 2 unmatched ones the minimum match was given as 82%

1st 20 Lines of your Example file

20220122 Fuzzy Match sample 001.xlsx
	E	F	G	H	I
1	UPC	FullDescription	DiscogsReleaseID	Description2	Similarity
2	54645665414	Black Ark Players, Lee Perry - Guidance (ltd. ed.) (picture disc)	r15418244	Guidance	0.8200
3	54645635516	Cutty Ranks - Grizzle / Cocoa Tea - Sonia (rhythm: "Punanny")	r505655	Grizzle / Cocoa Tea - Sonia	0.9026
4	54645634113	Cutty Ranks - Limb By Limb (Vocal); Cutty Ranks - Limb By Limb (Acapella) / Cutty Ranks - Limb By Limb (Hip Hop Mix) (rhythm: "Pitch")	r1913711	Limb By Limb (Vocal); Cutty Ranks - Limb By Limb (Acapella) / Cutty Ranks - Limb By Limb (Hip Hop Mix)	0.9638
5	54645635912	Dawn Penn - You Don't Love Me (No No No) / No No No Version (rhythm: "Dawn Penn - You Don't Love Me / AKA No No No")	r2811476	You Don't Love Me (No No No) / No No No Version	0.8845
6	54645636513	Devonte & Peter Metro - Say Wooee / Beenie Man - Bookshelf (rhythm: "Bookshelf")	r1664807	Say Wooee / Beenie Man - Bookshelf	0.8958
7	54645637718	Foxy Brown - Sorry; Version 1 / Version 2	r902855	Sorry; Version 1 / Version 2	0.9357
8	54645652414	Gregory Isaacs - Mr. Know It All (Extended Mix) / Ossie Hibbert, Revolutionaries - War Of The Stars	r5609275	Mr. Know It All (Extended Mix) / Ossie Hibbert, Revolutionaries - War Of The Stars	0.9672
9	54645637916	Gregory Isaacs - Rumours / Version (rhythm: "Telephone Love AKA Rumors")	r1584432	Rumours / Version	0.8400
10	54645636216	Ini Kamoze - Hot Stepper / Stepper Version	r656098	Hot Stepper / Stepper Version	0.9329
11	54645653510	Itals - Ina Dis Ya Time / Yellowman - Operation Eradication; Harbour View Rock (Instrumental)	r7003747	Ina Dis Ya Time / Yellowman - Operation Eradication; Harbour View Rock (Instrumental)	0.9825
12	6.01811E+11	Johnny Osbourne - Fally Ranking (Extended Mix) / Johnny Osbourne - Trechtown School (Extended Mix)	r12108077	Fally Ranking (Extended Mix) / Johnny Osbourne - Trechtown School (Extended Mix)	0.9654
13	54645637312	Lady G - Nuff Respect / Version (rhythm: "Telephone Love")	r1796465	Nuff Respect / Version	0.8743
14	6.01811E+11	Michael Prophet - Gunman (Extended Mix) / Michael Prophet - Cassandra (Extended Mix) (rhythm: "Michael Prophet - Gunman")	r2334396	Gunman (Extended Mix) / Michael Prophet - Cassandra (Extended Mix)	0.9063
15	54645634212	Michigan & Smiley - Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix) (rhythm: "Diseases AKA Golden Hen")	r1585354	Diseases (Extended Mix) / Nicodemus - Boneman Connection (Extended Mix)	0.8986
16	6.30283E+11	Reggae Regular - Where Is Jah (Extended Mix) / Reggae Regular - Black Star Liner (Extended Mix)	r14652106	Where Is Jah (Extended Mix) / Reggae Regular - Black Star Liner (Extended Mix)	0.9672
17	54645636117	Reggie Stepper - Kimbo King / Whining Skill (rhythm: "Stalag")	r2509059	Kimbo King / Whining Skill	0.9000
18	54645633611	Shabba Ranks - Wicked In Bed / Singing Melody - Groovy Kind Of Love	r1788202	Wicked In Bed / Singing Melody - Groovy Kind Of Love	0.9609
19	54645633512	Sister Nancy - Bam Bam / Stalag Version (rhythm: "Stalag")	r163058	Bam Bam / Stalag Version	0.8947
20	54645640916	Sizzla - Just One Of Those Days; Acoustic Mix / Solid As A Rock (rhythm: "Queen Majesty")	r818187	Just One Of Those Days; Acoustic Mix / Solid As A Rock	0.9375
21	7.25543E+11	Studio X - Los Kings Del Mambo (Digifunky's House Mix) / Studio X - Los Kings Del Mambo (DJ EFX's Hyped Live Mix) (orig. press)	r182011	Los Kings Del Mambo (Digifunky's House Mix) / Studio X - Los Kings Del Mambo (DJ EFX's Hyped Live Mix)	0.9630
Fuzzy Match

Unmatched lines

Book2
	A	B	C	D	E
1	UPC	FullDescription	DiscogsReleaseID	Description2	Similarity
2	5.29101E+12	10000 Maniacs - Planned Obsolescence: Live '88 (remastered)	r9882267		0.0000
3	5.02839E+12	Admiral Bailey - Best Of (20 tracks)	r11455690		0.0000
Sheet1

I gave the 3 column table the name tblComplete and the other single column tablethe name tblMatchUp.
It outputted to the active cell, so make sure that it is on a new sheet or to the right or below and data on the current sheet.

 

[JW00]

In the beginning you asked for an accuracy percentage between the columns & that is what we ended up with. I have uploaded the file to the Google Drive but this is just an explanation of what happens in the code.

None of the code affects your original data

New Function – type in E14

= WordMatchPercentage (B14, D14)

Code Order

Str = B

Str1 = D

Remove all characters except for letters, numbers & spaces from both columns

Str = "Say Wooee Beenie Man Bookshelf Devonte Peter Metro"

Str1 = "Say, Wodee, Bookshelf, Devonte, Tanto, Metro, Beenie, Man"

Create array of words from Str1

Say

Wodee

Bookshelf

Devonte

Tanto

Metro

Beenie

Man


Iterate though the list to check if the words are present in Str

Say Match

Wodee No Match (Str spelling Wooee)

Bookshelf Match

Devonte Match

Tanto No Match (Str includes Peter & not Tanto)

Metro Match

Beenie Match

Man Match

Calculate Match Percentage - Six words out of the eight Match

Column E = 75%


With the data provided you have 11,916 records that can be matched (2441 null records), out of those 9,315 are a 100% match, 11,249 are a >= 75% match.

If you enter the formula & drag it down it takes about a minute to complete the 14K rows, after you have all the data run the macro ‘FixValues’, that will just leave the values in column E, this stops the worksheet recalculating the formulas when you filter your data. If you want to leave the formulas, I suggest you switch the worksheet to manual calculation when you filter the data to prevent recalculation. This formula works perfectly well with the original data provided; you will just need to copy the module into your original sheet.