excel 2007Closed

Question

Hello, I have a list of 8500 names and I want a formula that can identify the cells where the names are the same. I do not have a list of known names that are the same. i.e. I want the formula to tell be James Brown is in cell a20 and c20.

Any help would be appreciated

Doug

rizvisa1 · Answer

Presumption all the names are in column A. You are checking in name in column C also exist in column D

Put this in cell d2

=IF(ISERROR(VLOOKUP(C2,A:A,1,FALSE)), "Nope Not in A column", "Eureka!!! Found ya")

dshean · Answer

I guess I didn't give an accurate description of the problem. Your solution works great if I have 2 separate columns of names and I am trying to compare names from 1 to the other, but I have column A with a customer number, column B with the customer name. What I am trying to isolate is all the names in column B that are the same but have different customer numbers in column A. What I am trying to do is flag them so I can consolidate all duplicate names under 1 customer number for each set of names that are the same.

Thanks for the valiant and amussing effort.

Doug

rizvisa1 · Answer

Well can you sort the data ? Also is data limited to two columns ?

rizvisa1 · Answer

Well ok so if name is in column B and numbers are in column C and column B is sorted, then

if you run this code, it will put in col E, found if it finds a name repeated. 
Also in col D it combine the numbers. See this does for u

sub whereisdup()

    lMaxRow = Cells(65536, 2).End(xlUp).Row
    
    lCheckedRow = 2
    checkName = Cells(lCurrentRow, 2)
    
    'the line below would  put the phone number from col c to col d
    Cells(lCheckedRow, 4) = Cells(lCheckedRow, 3)
    
    For lCurrentRow = 3 To lMaxRow
        
        If (Cells(lCurrentRow, 2) = "") Then Exit Sub
        
        If (Cells(lCurrentRow, 2) <> checkName) Then
        
            checkName = Cells(lCurrentRow, 2)
            Cells(lCurrentRow, 4) = Cells(lCurrentRow, 3)
            lCheckedRow = lCurrentRow
        
        Else
            Cells(lCheckedRow, 4) = ", " & Cells(lCurrentRow, 3)
            Cells(lCurrentRow, 5) = "Found Before"
        End If
        
    Next

end sub

dshean · Answer

Thank you for the suggestion but this seems beyond my level of expertise. Is this a macro? If so do I have to type in all the lines you provided with the same spacing & capitalization. If so are there spaces before and after the = sign and is that a number 1 in front MaxRow?

Doug

rizvisa1 · Answer

Yeah it is macro

to enter macro

press CRTL + F11 at same time, that open VBE

Then go to Insert and insert a module

copy all the from   sub whereisdup()   to End sub
Paste the code

go back to your sheet

Press ALT + F8 at same time

choose the macro "whereisdup" to run.

dshean · Answer

Ok I entered CTRL F11 but I have a couple of tabs that say insert 1) is by the Home tab at the top the other is to the right with the cells area. When I browse either one I don't see module anywhere.

Doug

rizvisa1 · Answer

oh call me stupid. I meant type ALT+F11

dshean · Answer

That works but I got an error message that gave me a debugging option which I selected. It highlights the 4th line which says checkName = Cells(1CurrentRow,2) any suggestions. I think we are close to the finish line.

Doug

rizvisa1 · Answer

Ok 
change this
lCheckedRow = 2
checkName = Cells(lCurrentRow, 2) 

to 

lCheckedRow = 2
checkName = Cells(lCheckedRow , 2)

Excel 2007

10 responses

Similar discussions

Subscribe To Our Newsletter!