Solved

macro to find compare cells contents from one colume to another

Posted on 2011-03-09
22
501 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:bpfsr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 5
  • +1
22 Comments
 
LVL 10

Expert Comment

by:peetm
ID: 35085913
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?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35085914
You don't need to write a macro for this. Simply use the vlookup() :)

Sid
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35085929
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35085961
Put this formula in Cell M1 and pull it down.

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

Sid
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35085976
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

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35085987
One more way which is faster than the above formula.

Put this in M1.

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

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35085992
Ardhendu: Didn't see this post as well.

Sid
0
 

Author Comment

by:bpfsr
ID: 35086026
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086042
>>> 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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086063
Sample attached.

Sid
sample.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086094
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
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35086188
I think both formulas and macros will get you the same results. can you please post a sample spreadsheet of your data?
0
 

Author Comment

by:bpfsr
ID: 35086275
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....
0
 

Author Comment

by:bpfsr
ID: 35086298
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://
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35086306
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 :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086317
>>>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
0
 

Author Comment

by:bpfsr
ID: 35088380
here the the sample
Scott-Project.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35088413
There is nothing in Col G? Do you want to compare it with Col A of Sheet Lookup?

Sid
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35088444
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.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35088564
If my understanding is correct then you are trying to match the base URLs. If this is true then try this macro.

Sub Find_Matches()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRowws1 As Long
    Dim TempSource As String
    Dim aCell As Range
    
    Set ws1 = Sheets("Master")
    Set ws2 = Sheets("Lookup")
    
    lastRowws1 = ws1.Range("F" & Rows.Count).End(xlUp).Row
    
    '~~> Loop through rows in Col F
    For i = 2 To lastRowws1
        TempSource = Replace(ws1.Range("F" & i).Value, "http://", "")
        pos = InStr(1, TempSource, "/")
        
        If pos <> 0 Then
            TempSource = Mid(TempSource, 1, pos - 1)
        End If
        
        '~~> The base URL in Lookup Sheet
        Set aCell = ws2.Columns(1).Find(What:=TempSource, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        '~~> Output the values in Col M
        If Not aCell Is Nothing Then
            ws1.Range("M" & i).Value = "Duplicate"
        Else
            ws1.Range("M" & i).Value = "Unique"
        End If
    Next i
End Sub

Open in new window


Sid
0
 

Author Comment

by:bpfsr
ID: 35088569
sorry I am comparing column "f" in Master to "a" in "lookup"
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35088579
>>>>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
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question