VBA to compare two cells values

SamPaulRoger

New Member
Joined
Jul 28, 2018
Messages
12
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...

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Auto Populated[/TD]
[TD="align: center"]Manually Populated[/TD]
[TD="align: center"]Script Output[/TD]
[/TR]
[TR]
[TD]Requirements[/TD]
[TD]Completed[/TD]
[TD]Remaining[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]AB[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD]ABCDE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]A[/TD]
[TD]BC[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]AC[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD][/TD]
[TD]ABCD[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

Code:
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
ABC
1RequirementsCompletedRemaining
2ABCDABCD
3ABCDEABCDE
4ABCABC
5ABCACB
6AA
7ABCDABCD
Sheet1
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
ABC
1RequirementsCompletedRemaining
2ABCDABCD
3ABCDEABCDE
4ABCABC
5ABCACB
6AA
7ABCDABCD
Sheet3
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top