Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

macro to find compare cells contents from one colume to another

Posted on 2011-03-09
22
Medium Priority
?
514 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: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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

927 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