Deleting this duplicates without shifting the cells

[Coyotex3]

How can I accomplish this? Column B will always have duplicate values and I want to remove the duplicates but with a catch.

Book1
	A	B	C
1	Order Type	Name	Number
2	First	John	45654
3	Second	John	45665
4	Third	John	45878
5	First	Jack	56887
6	Second	Jack	54513
7	Third	Jack	15687
8	First	James	78954
9	Second	James	65895
10	Third	James	23564
11	First	Jane	23545
12	Second	Jane	12562
13	Third	Jane	12564
14	First	Jose	12357
15	Second	Jose	45879
16	Third	Jose	15877
17	First	Helen	15448
18	Second	Helen	65897
19	Third	Helen	78884
20	fourth	Helen	45658
21	First	James	87945
22	Second	James	46593
23	Third	James	32148
Sheet4

I want the end result to look like this. Essentially trying to delete duplicate values on Column B ONLY IF they are in successive rows. i,e if I see James on Rows 2,3,4, 21,22,23 I want to delete James from Rows 3,4 and 22,23 without shifting the cells to look like this

Book1
	A	B	C	D
1	Order Type	Name	Number
2	First	John	45654
3	Second		45665
4	Third		45878
5	First	Jack	56887
6	Second		54513
7	Third		15687
8	First	James	78954
9	Second		65895
10	Third		23564
11	First	Jane	23545
12	Second		12562
13	Third		12564
14	First	Jose	12357
15	Second		45879
16	Third		15877
17	First	Helen	15448
18	Second		65897
19	Third		78884
20	fourth		45658
21	First	James	87945
22	Second		46593
23	Third		32148
24
Sheet4

Any thoughts?

 

[Ezguy4u]

Coyotex it sounds like my program works but you still have an issue with your data. Your question of "John Doe" seems like it is fixable. Can you post an example of input and output? Just a short example.

 

[Coyotex3]

Coyotex it sounds like my program works but you still have an issue with your data. Your question of "John Doe" seems like it is fixable. Can you post an example of input and output? Just a short example.

Your program ABSOLUTELY works and was a solution. The issue that I'm having is that sometimes the names are like this

Book1
	A	B	C	D	E
1	Order Type	Name	Number
2	First	John	45789
3	Second	JOHN	45789
4	Third	John	45789
5	First	Jack	45789
6	Second	Jack	45789
7	Third	Jack	45789
8	First	James	45789
9	Second	Jams	45789
10	Third	James	45789
11	First	Jane	45789
12	Second	.Jane	45789
13	Third	Jane	45789
14	Fourth	Jane	45789
15	First	Jose	45789
16	Second	Jose	45789
17	Third	Jose	45789
18
19
Sheet1

They might have a grammatical error like a typo or spacing etc. I would like for the macro to recognize that if possible.

 

[Ezguy4u]

Coyotex Ok here’s the deal. We can fix upper and lower case to the proper case. We can trim extra spaces in front of a name. Correcting a misspelled name or extra punctuation is more difficult. In my opinion it kind of boils down to, how does a program know there isn’t a person named Jams? There are just too many punctuations. But there is good news. Now this program will do the proper case and extra spaces. So, if you run the program one-time, misspelled names and punctuations will stand out like a sore thumb. You would correct the errors, and run the program one more time. Fixing misspelled names could start with how these names are generated and installed in the cell. Let’s go with what we have now and if later you still have problems that are troublesome, then post those problems and see what solutions we come up with. Sometimes the A plus students weigh in and come up with brilliant solutions.

Option Explicit

Sub Clear4()
Dim CellCnt As Integer
Dim i As Integer

Application.ScreenUpdating = False

CellCnt = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To CellCnt
    Cells(i, 2) = Trim(Cells(i, 2))
    Cells(i, 2) = StrConv(Cells(i, 2), vbProperCase)
Next i
    
For i = CellCnt To 2 Step -1

If Cells(i, 2) Like Cells(i - 1, 2) Then
    Selection.ClearContents
End If

Next i
Application.ScreenUpdating = True

End Sub

 

[Ezguy4u]

Coyotex Ok here’s the deal. We can fix upper and lower case to the proper case. We can trim extra spaces in front of a name. Correcting a misspelled name or extra punctuation is more difficult. In my opinion it kind of boils down to, how does a program know there isn’t a person named Jams? There are just too many punctuations. But there is good news. Now this program will do the proper case and extra spaces. So, if you run the program one-time, misspelled names and punctuations will stand out like a sore thumb. You would correct the errors, and run the program one more time. Fixing misspelled names could start with how these names are generated and installed in the cell. Let’s go with what we have now and if later you still have problems that are troublesome, then post those problems and see what solutions we come up with. Sometimes the A plus students weigh in and come up with brilliant solutions.

Option Explicit

Sub Clear4()
Dim CellCnt As Integer
Dim i As Integer

Application.ScreenUpdating = False

CellCnt = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To CellCnt
    Cells(i, 2) = Trim(Cells(i, 2))
    Cells(i, 2) = StrConv(Cells(i, 2), vbProperCase)
Next i
   
For i = CellCnt To 2 Step -1

If Cells(i, 2) Like Cells(i - 1, 2) Then
    Cells(i, 2).ClearContents
End If

Next i
Application.ScreenUpdating = True

End Sub

 

[Ezguy4u]

Coyotex I am having a hard time correcting a mistake. The line should be Cells(i, 2).ClearContents and not Selection.ClearContents. I hope this doesn't confuse the subject.

 

[Coyotex3]

Honestly this is more than enough. I have a sheet with like 10000 rows. This code deletes most repeat values and like you said the others stick out. I don’t foresee any more bumps in the road. I truly appreciate the code!

 

[Ezguy4u]

Coyotex thank you for your feed back and if there is anything else you need, don't hesitate to ask.

 

[Coyotex3]

Coyotex thank you for your feed back and if there is anything else you need, don't hesitate to ask.

Truly, I would love to learn how get better at this. Like I watch Youtube videos but I do not retain much of the information. How do most people go about learning and getting better?

 

[Ezguy4u]

Coyotex that is a great question. You ask really great questions. Let me expound on what I know and give my opinions. First, I am going to say there are 2 parts to MS Excel. The first part would be entering formulas into excel cells. Math formulas are a good start. Your local library should have books on this subject. If you find a good one buy it. Next part would be IF statements. You just need to start with a simple syntax or structure of an IF condition. Once you have some time on those part then you get to move onto Visual Basic for Application or VBA. Now VBA, in my opinion, never ends. The best you can hope for is just to be able to perform simple programs in the beginning. Where I am at is being able to look at a program and try to understand how it works. I am always amazed at some of the people here on Mr Excel and how they program. What takes me 5 or 6 lines of code, they will do with 1 or 2 lines of code. So to conclude, my suggestion is look for books at the library, maybe look at adult education classes offering excel and just keep using excel.

 

[Coyotex3]

Coyotex that is a great question. You ask really great questions. Let me expound on what I know and give my opinions. First, I am going to say there are 2 parts to MS Excel. The first part would be entering formulas into excel cells. Math formulas are a good start. Your local library should have books on this subject. If you find a good one buy it. Next part would be IF statements. You just need to start with a simple syntax or structure of an IF condition. Once you have some time on those part then you get to move onto Visual Basic for Application or VBA. Now VBA, in my opinion, never ends. The best you can hope for is just to be able to perform simple programs in the beginning. Where I am at is being able to look at a program and try to understand how it works. I am always amazed at some of the people here on Mr Excel and how they program. What takes me 5 or 6 lines of code, they will do with 1 or 2 lines of code. So to conclude, my suggestion is look for books at the library, maybe look at adult education classes offering excel and just keep using excel.

Thank you @Ezguy4u really good advice. Right now I'm trying to comprehend what each line is doing but it is hard to understand at times. I'm amazed at how people like Fluff answer questions in an instant. I wish I had those skills