[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

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.
0
bpfsr
Asked:
bpfsr
  • 11
  • 5
  • 5
  • +1
1 Solution
 
peetmCommented:
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
 
SiddharthRoutCommented:
You don't need to write a macro for this. Simply use the vlookup() :)

Sid
0
 
Ardhendu SarangiSr. Project ManagerCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SiddharthRoutCommented:
Put this formula in Cell M1 and pull it down.

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

Sid
0
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
SiddharthRoutCommented:
One more way which is faster than the above formula.

Put this in M1.

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

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

Sid
0
 
bpfsrAuthor Commented:
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
 
SiddharthRoutCommented:
>>> 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
 
SiddharthRoutCommented:
Sample attached.

Sid
sample.xlsx
0
 
SiddharthRoutCommented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
I think both formulas and macros will get you the same results. can you please post a sample spreadsheet of your data?
0
 
bpfsrAuthor Commented:
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
 
bpfsrAuthor Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
SiddharthRoutCommented:
>>>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
 
bpfsrAuthor Commented:
here the the sample
Scott-Project.xlsm
0
 
SiddharthRoutCommented:
There is nothing in Col G? Do you want to compare it with Col A of Sheet Lookup?

Sid
0
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
SiddharthRoutCommented:
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
 
bpfsrAuthor Commented:
sorry I am comparing column "f" in Master to "a" in "lookup"
0
 
SiddharthRoutCommented:
>>>>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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now