Deleting this duplicates without shifting the cells

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
How can I accomplish this? Column B will always have duplicate values and I want to remove the duplicates but with a catch.

Book1
ABC
1Order TypeNameNumber
2FirstJohn45654
3SecondJohn45665
4ThirdJohn45878
5FirstJack56887
6SecondJack54513
7ThirdJack15687
8FirstJames78954
9SecondJames65895
10ThirdJames23564
11FirstJane23545
12SecondJane12562
13ThirdJane12564
14FirstJose12357
15SecondJose45879
16ThirdJose15877
17FirstHelen15448
18SecondHelen65897
19ThirdHelen78884
20fourthHelen45658
21FirstJames87945
22SecondJames46593
23ThirdJames32148
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
ABCD
1Order TypeNameNumber
2FirstJohn45654
3Second45665
4Third45878
5FirstJack56887
6Second54513
7Third15687
8FirstJames78954
9Second65895
10Third23564
11FirstJane23545
12Second12562
13Third12564
14FirstJose12357
15Second45879
16Third15877
17FirstHelen15448
18Second65897
19Third78884
20fourth45658
21FirstJames87945
22Second46593
23Third32148
24
Sheet4


Any thoughts?
 

Ezguy4u

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
ABCDE
1Order TypeNameNumber
2FirstJohn45789
3SecondJOHN45789
4ThirdJohn45789
5FirstJack45789
6Second Jack45789
7ThirdJack45789
8FirstJames45789
9SecondJams45789
10ThirdJames45789
11FirstJane45789
12Second.Jane45789
13ThirdJane45789
14FourthJane45789
15FirstJose45789
16SecondJose45789
17ThirdJose45789
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

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
Coyotex thank you for your feed back and if there is anything else you need, don't hesitate to ask.
 

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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

Active Member
Joined
Feb 10, 2010
Messages
273
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,638
Messages
5,931,154
Members
436,778
Latest member
TCarr1958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top