VBA: Compare Strings for Percentage

[ludlow]

I am complete lost on this one and hoping for some assistance.

I have a workbook with sheet1 and sheet2. In each sheet, the comparable data in located in column A. I would like a code that compare and match the sheet 1 data against sheet2. The results would be located on sheet3 with the percentage match.

Thanks in advance.

Example:
Sheet 1

HEADER

tommy123

peters

susan6783

edwards

<TBODY>
</TBODY>

Sheet2

HEADER

tommy122

susan6733

peters

edwards123

<TBODY>
</TBODY>

Sheet3: Results

HEADER 1	HEADER2	PERCENTAGE MATCH
tommy123	tommy122	87.5%
peters	peters	100%
susan6783	susan6733	88.89%
edwards	edwards123	70%

<TBODY>
</TBODY>

 

[ludlow]

I checked the other threads out there, but I cannot follow what is going on.

 

[Snakehips]

ludlow,

Copy this code to a code module and give it a try...

Sub StrPercent()
Application.ScreenUpdating = False
LR = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet3").Range("A1:A" & LR).Value = Sheets("Sheet1").Range("A1:A" & LR).Value
Sheets("Sheet3").Range("B1:B" & LR).Value = Sheets("Sheet2").Range("A1:A" & LR).Value
For r = 2 To LR
If Trim(Cells(r, "A").Value) = Trim(Cells(r, "B").Value) Then
Cells(r, "C").Value = 1
GoTo Done
End If
Str1 = Trim(Cells(r, "A").Value)
Str2 = Trim(Cells(r, "B").Value)
Len1 = Len(Str1)
Len2 = Len(Str2)
MaxLen = WorksheetFunction.Max(Len1, Len2)
MinLen = Len1
If MaxLen = Len1 Then MinLen = Len2
Same = 0
For c = 1 To MinLen
If Mid(Str1, c, 1) = Mid(Str2, c, 1) Then Same = Same + 1
Next c
Cells(r, "C").Value = Same / MaxLen
Done:
Next r
Application.ScreenUpdating = True
End Sub

NB Format Col C as %

Hope that helps.

 

[ludlow]

Snakehips,
I tried the above code, but the results were less than favorable. Although it was not mentioned before, there are thousands of rows per each worksheet. Nonetheless, here is the problem I am running into. Sheet2 datum, cell A2 is comparing itself against sheet1 datum, cell A2 versus trying to find the closest match on sheet1.

The results once the macro is executed. The below data is present on the 3rd sheet named "Results".
SHEET1 SHEET2

HEADER	HEADER	PERCENTAGE
tom123	tom456	50%
andy	jab	25%
hello	by	0%
paul	andy	25%
tim	tom123	33%

<TBODY>
</TBODY>

Expected Report
SHEET1 SHEET2

HEADER	HEADER	PERCENTAGE
tom123	tom123	100%
andy	andy	100%
hello	by	0%
paul	jab	25%
tim	tom456	67%

<TBODY>
</TBODY>

 

[Snakehips]

ludlow,

I accept that the original code may not have done exactly what you required. However, it did replicate the result expected from your first post.
Having seen your last post, #4 I think I now have a better understanding of what is required.

Here is revised code and a sample of the result it produces.
It maybe slow to process your thousands of rows but it is the best I can come up with.

Excel 2007
	A	B	C
1	Header	Header	PERCENTAGE MATCH
2	tommy123	tommy122	87.50%
3	peters	peters	100.00%
4	susan6783	susan6733	88.89%
5	edwards	edwards123	100.00%
6	tom123	tom456	50.00%
7	andy	jab	25.00%
8	hello	by	0.00%
9	paul	andy	25.00%
10	tim	tom123	66.67%
Sheet3

Sub StrPercent()
Application.ScreenUpdating = False
LR = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet3").Range("A1:A" & LR).Value = Sheets("Sheet1").Range("A1:A" & LR).Value
Sheets("Sheet3").Range("B1:B" & LR).Value = Sheets("Sheet2").Range("A1:A" & LR).Value
For r = 2 To LR
If Trim(Cells(r, "A").Value) = Trim(Cells(r, "B").Value) Then
Cells(r, "C").Value = 1
GoTo Done
End If
Str1 = Trim(Cells(r, "A").Value)
Str2 = Trim(Cells(r, "B").Value)
Len1 = Len(Str1)
Len2 = Len(Str2)
Same = 0
For c = 1 To Len2
If InStr(1, Str1, Mid(Str2, c, 1), 1) Then
Same = Same + 1
Str1 = Replace(Str1, Mid(Str2, c, 1), "*", 1, 1)
End If
Next c
Cells(r, "C").Value = Same / Len1
Done:
Next r
Application.ScreenUpdating = True
End Sub

Hope that helps.

 

[ludlow]

I def'ly didn't make the first question clear, and for that I apologize. At the same time, The first code does provide another use, for which I am sure I will use in the near future.

 

[ludlow]

Tony,
I tried the new code - no luck. Additionally, I got an error with this line of code, "Cells(r, "C").Value = Same / Len1"

I appreciate your assistance. I just don't know where to turn now. I tried searching the web and no luck there either.

 

[Snakehips]

ludlow,


My apologies!!
In trying to get a solution for the % worked out, I was calling the code whilst Sheet3 was active and I overlooked the fact that it would fail if run with sheet 1 or 2 as the active sheet. Hopefully that is the issue!!???

Put the following, revised,code in a code module and try it on a small data set.
It should now run from any sheet.

Sub StrPercent()
Application.ScreenUpdating = False
LR = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet3")
.Range("A1:A" & LR).Value = Sheets("Sheet1").Range("A1:A" & LR).Value
.Range("B1:B" & LR).Value = Sheets("Sheet2").Range("A1:A" & LR).Value
For r = 2 To LR
If Trim(.Cells(r, "A").Value) = Trim(.Cells(r, "B").Value) Then
.Cells(r, "C").Value = 1
GoTo Done
End If
Str1 = Trim(.Cells(r, "A").Value)
Str2 = Trim(.Cells(r, "B").Value)
Len1 = Len(Str1)
Len2 = Len(Str2)
Same = 0
For c = 1 To Len2
If InStr(1, Str1, Mid(Str2, c, 1), 1) Then
Same = Same + 1
Str1 = Replace(Str1, Mid(Str2, c, 1), "*", 1, 1)
End If
Next c
.Cells(r, "C").Value = Same / Len1
Done:
Next r
End With
Application.ScreenUpdating = True
End Sub

Hope that sorts it.
Please let me know.

 

[ludlow]

Tony,
We are getting closer. To say the least, this is a complicated code to write. Stated below are the following:
SHEET1: Data in the first tab, columnA
SHEET2: Data in the second tab, columnA
SHEET3: The results of the script -tab1 data in columnA along side tab2 data in columnB with the Matched percentage.

NOTE: As you can see, the information from SHEET1, cellA2 was compared to SHEET2, cellA2, versus finding the identical or the closest match. If all went well, then the data would have been located and placed in the adjacent columnB (in SHEET3, columnB, cellA2) versus cellB10. The expected results would be the same as what is in the "sheet" named Expected Results.

SHEET1</SPAN>	SHEET2</SPAN>		SHEET(3) </SPAN>		EXPECTED RESULTS</SPAN>
HEADER(1)</SPAN>	HEADER(2)</SPAN>	HEADER(1)</SPAN>	HEADER(2)</SPAN>	MATCH %</SPAN>		HEADER(1)</SPAN>	HEADER(2)</SPAN>	MATCH %</SPAN>
YV4CZ982871394720</SPAN>	ZDM1SB3R5WB006527</SPAN>	YV4CZ982871394720</SPAN>	ZDM1SB3R5WB006527</SPAN>	35.29%</SPAN>		YV4CZ982871394720</SPAN>	YV4CZ982871394720</SPAN>	100.00%</SPAN>
YV4CZ982581485025</SPAN>	YV4CZ982171392937</SPAN>	YV4CZ982581485025</SPAN>	YV4CZ982171392937</SPAN>	58.82%</SPAN>		YV4CZ982581485025</SPAN>	YV4CZ982171392937</SPAN>	58.82%</SPAN>
YV4CZ982581428999</SPAN>	YV4CZ982081480895</SPAN>	YV4CZ982581428999</SPAN>	YV4CZ982081480895</SPAN>	82.35%</SPAN>		YV4CZ982581428999</SPAN>	ZDM1SB3R5WB006527</SPAN>	22.20%</SPAN>
YV4CZ982371396147</SPAN>	YV4CZ982081460895</SPAN>	YV4CZ982371396147</SPAN>	YV4CZ982081460895</SPAN>	70.59%</SPAN>		YV4CZ982371396147</SPAN>	YV4CZ982081460895</SPAN>	70.59%</SPAN>
YV4CZ982191497917</SPAN>	YV4952DZ8D2400277</SPAN>	YV4CZ982191497917</SPAN>	YV4952DZ8D2400277</SPAN>	58.82%</SPAN>		YV4CZ982191497917</SPAN>	YV4952DZ8D2400277</SPAN>	58.82%</SPAN>
YV4CZ982081460895</SPAN>	YV4952CY0D1637781</SPAN>	YV4CZ982081460895</SPAN>	YV4952CY0D1637781</SPAN>	64.71%</SPAN>		YV4CZ982081460895</SPAN>	YV4CZ982081480895</SPAN>	100.00%</SPAN>
YV4CZ852881477399</SPAN>	YV1SJ52Y042412235</SPAN>	YV4CZ852881477399</SPAN>	YV1SJ52Y042412235</SPAN>	47.06%</SPAN>		YV4CZ852881477399</SPAN>	YV1SJ52Y042412235</SPAN>	47.06%</SPAN>
YV4CY982X71384872</SPAN>	YV1NC63D34J047242</SPAN>	YV4CY982X71384872</SPAN>	YV1NC63D34J047242</SPAN>	58.82%</SPAN>		YV4CY982X71384872</SPAN>	YV1NC63D34J047242</SPAN>	58.82%</SPAN>
YV4CY982171384856</SPAN>	YV4CZ982871394720</SPAN>	YV4CY982171384856</SPAN>	YV4CZ982871394720</SPAN>	70.59%</SPAN>		YV4CY982171384856</SPAN>	YV4952CY0D1637781</SPAN>	65.54%</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[ludlow]

In trying to figure out the best solution to this problem, I had a friend, who is a programmer (who is not familiar with VBA), and this is what his responsed...

"Yeah I see what you mean. I could code what you need in COBOL, but that will be of very little help to you... you need match-merge logic, where the program/macro takes two datasets, Set A and Set B, sorts them ascending, starts from the top of each dataset and steps down, comparing them along the way.</SPAN>

If there's a perfect match between the value of Record A and Record B, denote that as a counter or whatever, read the next record from BOTH files.</SPAN>

If there is not a perfect match</SPAN>
If Record A > Record B, in your case, it looks like you want to find the percentage of the digits that match between Rec A and Rec B.</SPAN>
But my question is, what if the NEXT Record in Set B is a PERFECT match to your current Record A, and you just have one extra non-matching row in Set B?</SPAN>
So to my way of thinking, I would write Record B to a "Record B Save Area" and then read the next record from Set B, and do another compare... A and B might match now.</SPAN>

If Record B > Record A, write Record A to a "Record A Save Area" and then read the next record from Set A, and do another compare.</SPAN>

By the time you are done with this, you have:</SPAN>
A count of how many perfect matches you have A "Record A Save Area", containing all of the records from Set A which had no PERFECT match on Set B A "Record B Save Area", containing all of the records from Set B which had no PERFECT match on Set A</SPAN>

Those save areas are both, by default, already in Ascending order.</SPAN>

Now you take on the task of finding the match percentages between Record A Save Area and Record B Save Area.</SPAN>
But this is inherently fuzzy logic, what if there are a different number of records in A and B, What if A has the number 12345, and B has the numbers 12344 and 12346...</SPAN>
Both records in B are only 1 digit away from matching A... which one do you choose to assign a percentage to?</SPAN>
What if A has the number 12345, and B has the numbers 12344 and 54321...</SPAN>
numerically, 12344 is only 1 digit away from 12345, but 54321 contains exactly the same digits as 12345, so is that a match?"</SPAN>