VBA to compare two cells values

[SamPaulRoger]

Hi,

I am looking to have a spreadsheet that is updated from a database, and then someone manually types in what has been completed, I would like to have a macro where it would compare the values between two cells then output the what is missing. For example, in cell A2 I have "ABCD" then in B2 I have "AB" it would look at the specific Alpha characters, and output "CD" in C2. Also not sure if it matters or not, however, I will have to do this for approximately 20,000 rows...

Auto Populated	Manually Populated	Script Output
Requirements	Completed	Remaining
ABCD	AB	CD
ABCDE	ABCDE
ABC	A	BC
ABC	AC	B
A		A
ABCD		ABCD

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming everything is is based on single characters as it appears to be, you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function Remaining(sReq As String, sComp As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "(" & Replace(Trim(Replace(StrConv(sComp, vbUnicode), ChrW(0), " ")), " ", "|") & ")"
  Remaining = RX.Replace(sReq, "")
End Function

Excel Workbook
	A	B	C
1	Requirements	Completed	Remaining
2	ABCD	AB	CD
3	ABCDE	ABCDE
4	ABC	A	BC
5	ABC	AC	B
6	A		A
7	ABCD		ABCD
Sheet1

 

[SamPaulRoger]

That worked like a charm, would it be possible for you to break down the code with a few comments, I would like to understand what this is doing?

 

[Peter_SSs]

If you don't know about "Regular Expressions" you will need to do some research on that. Not possible to teach that in a few sentences.

The gist of it is to take the column B value and insert "|" characters between & surround with parentheses. So for row 2 we get "(A|B)"
That is the "Pattern" for the regular expression to look for and means look for "A" or "B"

Remaining = RX.Replace(sReq, "")

then takes the column A value (sReq) and replaces any instance of "A" or "B" in the column A text with "", effectively eliminating them and leaving the unfound characters.

 

[Peter_SSs]

Note that this can also be done with standard worksheet functions, if you have Office 365, though I suspect it would make the worksheet very lethargic if calculating a lot of rows like you have. However, for the record, this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C
1	Requirements	Completed	Remaining
2	ABCD	AB	CD
3	ABCDE	ABCDE
4	ABC	A	BC
5	ABC	AC	B
6	A		A
7	ABCD		ABCD
Sheet3

 

[SamPaulRoger]

Greatly appreciate it Thank you

 

[Peter_SSs]

Greatly appreciate it Thank you

You're welcome.