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>

 

[Snakehips]

ludlow,

Can you confirm that your expected result in post #9 is correct?
It's quite possible that I have yet to fully grasp the principle by which you are comparing these strings.
However, I would question the items below...

Excel 2007

	G	H	I	J
2	YV4CZ982871394720	YV4CZ982871394720	100.00%
3	YV4CZ982581485025	YV4CZ982171392937	58.82%
4	YV4CZ982581428999	ZDM1SB3R5WB006527	22.20%
5	YV4CZ982371396147	YV4CZ982081460895	70.59%
6	YV4CZ982191497917	YV4952DZ8D2400277	58.82%
7	YV4CZ982081460895	YV4CZ982081480895	100.00%	?????
8	YV4CZ852881477399	YV1SJ52Y042412235	47.06%
9	YV4CY982X71384872	YV1NC63D34J047242	58.82%
10	YV4CY982171384856	YV4952CY0D1637781	65.54%	?????

<TBODY>
</TBODY>

Sheet1​

I have revised code, which I will not post until I know that it is providing a solution, that yields the following result on your post #9 data.
Is this anything like what you want?

Excel 2007

	A	B	C
1	Header	Header	PERCENTAGE MATCH
2	YV4CZ982871394720	YV4CZ982871394720	100.00%
3	YV4CZ982581485025	YV4CZ982171392937	58.82%
4	YV4CZ982581428999	YV4CZ982081480895	82.35%
5	YV4CZ982371396147	ZDM1SB3R5WB006527	35.29%
6	YV4CZ982191497917	YV4952DZ8D2400277	58.82%
7	YV4CZ982081460895	YV4CZ982081460895	100.00%
8	YV4CZ852881477399	YV1SJ52Y042412235	47.06%
9	YV4CY982X71384872	YV1NC63D34J047242	58.82%
10	YV4CY982171384856	YV4952CY0D1637781	82.35%

<TBODY>
</TBODY>

Sheet3​

 

[ludlow]

With regards to post 9:
I copied the wrong value in H7 - as far the other one, I just placed a random percent there. In the example provided in post #9, I was trying to illustrate that the coloured coded values should have moved to the adjacent cell in columnA.


Per your last code:
If the original data were set to resemble the one below and the finished product looked like the one below that, then we have success.


Original Data (Before Macro)

1</SPAN>	Header1 - columnA</SPAN>	Header2 - columnB</SPAN>
2</SPAN>	YV4CZ982871394720</SPAN>	YV4CZ982081460895</SPAN>
3</SPAN>	YV4CZ982581485025</SPAN>	YV4CZ982081480895</SPAN>
4</SPAN>	YV4CZ982581428999</SPAN>	YV4CZ982171392937</SPAN>
5</SPAN>	YV4CZ982371396147</SPAN>	YV1SJ52Y042412235</SPAN>
6</SPAN>	YV4CZ982191497917</SPAN>	ZDM1SB3R5WB006527</SPAN>
7</SPAN>	YV4CZ982081460895</SPAN>	YV1NC63D34J047242</SPAN>
8</SPAN>	YV4CZ852881477399</SPAN>	YV4952DZ8D2400277</SPAN>
9</SPAN>	YV4CY982X71384872</SPAN>	YV4CZ982871394720</SPAN>

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

Finished Data (After Macro)
Note: In reviewing the before and after affects of columnB, one can see that the values - highlighted in blue - moved to their closest matched counterpart in columnA. The rest were relocated to highest matched counterpart.

1</SPAN>	Header1- columnA</SPAN>	Header2 - columnB</SPAN>	PERCENTAGE MATCH</SPAN>
2</SPAN>	YV4CZ982871394720</SPAN>	YV4CZ982871394720</SPAN>	100.00%</SPAN>
3</SPAN>	YV4CZ982581485025</SPAN>	YV4CZ982171392937</SPAN>	58.82%</SPAN>
4</SPAN>	YV4CZ982581428999</SPAN>	YV4CZ982081480895</SPAN>	82.35%</SPAN>
5</SPAN>	YV4CZ982371396147</SPAN>	ZDM1SB3R5WB006527</SPAN>	35.29%</SPAN>
6</SPAN>	YV4CZ982191497917</SPAN>	YV4952DZ8D2400277</SPAN>	58.82%</SPAN>
7</SPAN>	YV4CZ982081460895</SPAN>	YV4CZ982081460895</SPAN>	100.00%</SPAN>
8</SPAN>	YV4CZ852881477399</SPAN>	YV1SJ52Y042412235</SPAN>	47.06%</SPAN>
9</SPAN>	YV4CY982X71384872</SPAN>	YV1NC63D34J047242</SPAN>	58.82%</SPAN>

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

 

[Snakehips]

Ludlow,

Sorry but I'm going to have to admit defeat on this !!