Solved

macro to find compare cells contents from one colume to another

Posted on 2011-03-09
22
465 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
  • 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:pari123
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
 
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:pari123
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 20

Expert Comment

by:pari123
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:pari123
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:pari123
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now