Link to home
Get AccessLog in
Avatar of bpfsr
bpfsrFlag for United States of America

asked on

macro to find compare cells contents from one colume to another

I am trying to write a macro that examines if the cells of one column have unique values as compared to the cells of another column. So basically I want to look at cell F1, then compare it to every cell in Column G. If it finds a duplicate value anywhere in Column G it will put "duplicate" in Cell M1, if not it will put "unique". I have started the macro and have the following:

Sub Macro1()
Dim i As Integer, Finder As Variant, SearchRange As Range, c As Variant

i = 1
Cells(i, 5).Select

Do Until Selection.Value = ""
Finder = Right((Selection.Value), (Len(Selection.Value) - 7))
Set SearchRange = ActiveSheet.Columns(7)
For Each c In SearchRange
    If c.Value = Finder Then
    Selection.Offset(0, 7).Value = "duplicate"
    Else: selections.Offset(0, 7).Value = "unique"
    End If
Next
i = i + 1
Loop
End Sub

However I keep getting a runtime error 13 - type mismatch at 'If c.Value = Finder Then'

any help sorting me out would be much appreciated. thanks.
Avatar of peetm
peetm
Flag of United Kingdom of Great Britain and Northern Ireland image

You can use something like typeof, e.g., If typeof Finder Is String Then ...

I assume that one of these (either side of the = ) is null perhaps - isn't there an IsNull() to test for that - if indeed it is the case?
You don't need to write a macro for this. Simply use the vlookup() :)

Sid
Avatar of Ardhendu Sarangi
Hi,

you are getting an error because your code - Len(Selection.Value) - 7 is returning negative values (value less than 0) which renders the right function as uncomputable.

Here's an easy way to do this.

 Ardhendu
Put this formula in Cell M1 and pull it down.

=IF(ISERROR(VLOOKUP(F1,G:G,1,0)),"Unique","duplicate")

Sid
sorry, here's the code.
Sub Macro1()
Dim strg As String
strg = Range("F1")
For i = 1 To Cells(65536, "G").End(xlUp).Row
If Range("G" & i) = strg Then
    Range("M1") = "Duplicate in cell " & Range("G" & i).Address
    Exit Sub
Else
    Range("M1") = "Unique"
End If
Next
End Sub

Open in new window

One more way which is faster than the above formula.

Put this in M1.

=IF(COUNTIF(G:G,F1)>0, "Duplicate","Unique")

Sid
Ardhendu: Didn't see this post as well.

Sid
Avatar of bpfsr

ASKER

SiddharthRout - vlookup will not work as I am looking to compare the values of each cell in Column F against the values of each and every cell in column g
>>> compare the values of each cell in Column F against the values of each and every cell in column g

If I understand you correctly then it will :)

Sid
Sample attached.

Sid
sample.xlsx
Sorry the above example was for countif as I mentioned in ID: 35085987.

Here is the example for Vlookup. I would suggest Countif as compared to vlookup.

Sid
sample.xlsx
I think both formulas and macros will get you the same results. can you please post a sample spreadsheet of your data?
Avatar of bpfsr

ASKER

I can't figure out why SiddharthRout but neither of your solutions is working. What I was really hoping for was somebody who could correct my existing macro, rather than starting from scratch....
Avatar of bpfsr

ASKER

pari123 - I have checked and re-checked, it is not returning a negative value. the value in the cell in question is a web adrress, the formula there is simply removing the http://
if you can post a sample worksheet, then perhaps we can fix your macro. sorry but its a bit hard to guess the data here :)
>>>I can't figure out why SiddharthRout but neither of your solutions is working. What I was really hoping for was somebody who could correct my existing macro, rather than starting from scratch....

Can you upload a sample workbook?

Sid
Avatar of bpfsr

ASKER

here the the sample
Scott-Project.xlsm
There is nothing in Col G? Do you want to compare it with Col A of Sheet Lookup?

Sid
which of the columns are you trying to compare here? I am sorry but your columns in the question above don't match with your data.
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of bpfsr

ASKER

sorry I am comparing column "f" in Master to "a" in "lookup"
>>>>sorry I am comparing column "f" in Master to "a" in "lookup"

Please check the code that I gave above and tell me if that is what you want?

Sid