String Comparison VBA

5 pts.
Microsoft Excel
I have two columns of string values in excel. They are part numbers so they are typically 6 - 10 characters and a mix of letters and numbers. The columns contain many like strings. That is, the part number is listed in both columns. I need a quick way to compare each string in one column with every string in the other column to remove the ones that appear twice. The part numbers are in random order in each column so I can't just scroll through and compare them. I was hoping to do this with some string comparison function in VBA. Any suggestions?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’ve written u a simple vb procedure that will do the following and i hope it will work:

1- will look for the repeated values and give’m different color
2- then u do what ever u want like delete or something.
3- u need to change Ranges addresses with the ones u have, I mean u will change Range(“b2:b8”) and
Range(“c2:c8”) in my code with the columns in ur excell workbook.
4- change the value of i with the starting row number, that is the first row number that has your data
for example if ur first “Part_No” is in cell say (D5) so i will be=5
5 – you have to notice I’m using Microsoft Excell 7 in other cases just let me know because the only differnce would be with colors i think
6- just go to “Macros” then “View Macors”” then select “Create” and give it any name and then copy the code inside the next procedure this means u just copy lines between Sub comparing() and Exit Sub

Dim Nextcell As Range, CompareCell As Range
Dim RangeToCheck As Range
Dim RangeToCompare As Range
Set RangeToCheck = Range(“b2:b8”)
Set RangeToCompare = Range(“c2:c8”)
i = 2
For Each Nextcell In RangeToCheck.Cells
For Each CompareCell In RangeToCompare.Cells
If Nextcell = CompareCell Then
Nextcell.Font.Color = -16776961
CompareCell.Font.Color = -11489280
MsgBox (“Repeat at row no. ” & i & ” value is ” & Nextcell)
End If
i = i + 1
End Sub

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • AsHappyAs
    I'm not certain that a VBA is called for. Here's a formula I wrote when comparing 2 lists, 1 of 15,000 lines deep and the other about 10,000 lines deep. It generates, on filtering, either a "0" or an error message "#NA". Assuming you have the Primary Data in column A (say 1000 lines deep) and Secondary Data in column B (with headings). In cell C2 under heading say "Difference", place the following formula which will be filled down to the last cell in Column B. :- =IF(MATCH(B2,$A$2:$A$1001,0)+TRUE,0) If you apply Autofiltering to Column C, all instances that generate a "0" will be a duplicate, and you can simply cull them in Column B (only). Having been assisted before in this forum I trust this is of help to someone, AsHappyAs.
    75 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: