Excel 2007 [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

10 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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")
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well can you sort the data ? Also is data limited to two columns ?
I have all the names in clolumn B sorted from A-Z, I am trying to avoid going through the whole list to pick out the duplicates. I guess another solution would be to have a statement to remove the unique names leaving only the duplicates if that's possible.

Doug
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
oh call me stupid. I meant type ALT+F11
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok
change this
lCheckedRow = 2
checkName = Cells(lCurrentRow, 2)


to

lCheckedRow = 2
checkName = Cells(lCheckedRow , 2)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!