remove duplicate with exceptional

[kelvin_9]

hello everyone,

i was wondering can i amend my code below into something that it will not remove duplicate if "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" was found in column B?

here is my code:
Columns("B:B").Select
Selection.Copy
Columns("Q:Q").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$Q:$Q").RemoveDuplicates Columns:=1, Header:=xlNo

Book1
	B	C	D	E	F	G
1	Name	Person No.		JOB	Start Time	End Time
2	Hillary	18234			8:45	18:15
3	Oscar	18262			11:30	21:00
4	Miki	18376			11:30	21:00
5	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71083081			13:15	22:45
6	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71083267			13:15	22:45
Sheet1

thank you very much for your guidance

 

[Jeffrey Mahoney]

Please try this:

Sub CopyAndRemoveDups()
  Dim Cel As Range
  Dim Rng As Range
  Dim m As Long
  
  Range("Q:Q").Value = Range("B:B").Value
  
  Set Rng = Intersect(Range("Q:Q"), ActiveSheet.UsedRange)
  
  For Each Cel In Rng
    If Cel.Value <> "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" Then
      m = Application.CountIf(Rng, Cel.Value)
      If m > 1 Then
        Cel.ClearContents
      End If
    End If
  Next Cel
  Rng.SpecialCells(xlCellTypeBlanks).Select
  Selection.Delete Shift:=xlUp
        
      
End Sub

 

[Peter_SSs]

it will not remove duplicate if "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" was found in column B?

I'm not entirely sure how to interpret that. If the code below does not do what you want could you please post a slightly larger set of sample data and the expected results and explain again in relation to that new sample?

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

Sub Test()
  Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy Range("Q1")
  With Range("Q1", Range("Q" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#=""UNASSIGNEDSHIFT UNASSIGNEDSHIFT"",#&""$$$""&ROW(#),IF(#="""","""",#))", "#", .Address))
    .RemoveDuplicates Columns:=1, Header:=xlNo
    .Replace What:="$$$*", Replacement:="", LookAt:=xlPart
  End With
End Sub

 

[kelvin_9]

thank you very much for your reply Jeffrey Mahoney and Peter_SSs

both solution can slove my question prefectly!!

thank you very much for your guidance

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[kelvin_9]

You're welcome. Glad we could help. Thanks for the follow-up.

Hi

feel so sorry to interrupt again
when there are more than 1 start time and end time for "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" from the source
the result will only return the first one of start time and end time, can this be fixed?
source:

Book1
	B	C	D	E	F	G
1	Name	Person No.		JOB	Start Time	End Time
2	Hillary	18234			8:45	18:15
3	Oscar	18262			11:30	21:00
4	Miki	18376			11:30	21:00
5	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71480185			9:15	18:45
6	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71480187			9:15	18:45
7	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71480195			11:30	21:00
8	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71480251			11:30	21:00
9	UNASSIGNEDSHIFT UNASSIGNEDSHIFT	UNASSIGNEDSHIFT71480253			11:30	21:00
Sheet1

after load:

Book1
	A	B	C	D	E	F
1	Name	JOB	Start Time	End Time	Actual Hour	Name
5	UNASSIGNEDSHIFT71480195		9:15	21:00	11	UNASSIGNEDSHIFT UNASSIGNEDSHIFT
6	UNASSIGNEDSHIFT71480195		9:15	21:00	11	UNASSIGNEDSHIFT UNASSIGNEDSHIFT
7	UNASSIGNEDSHIFT71480195		9:15	21:00	11	UNASSIGNEDSHIFT UNASSIGNEDSHIFT
8	UNASSIGNEDSHIFT71480195		9:15	21:00	11	UNASSIGNEDSHIFT UNASSIGNEDSHIFT
9	UNASSIGNEDSHIFT71480195		9:15	21:00	11	UNASSIGNEDSHIFT UNASSIGNEDSHIFT
10					#VALUE!
Sheet1

thank you very much for your guidance again

 

[Peter_SSs]

I don't understand the requirement.

 

[kelvin_9]

I don't understand the requirement.

if start time and end time only have 1 section, example 09:15-18:45, it is fine when filter set for duplicate remove without remove "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" criteria

however, if start time and end time have more than 1 section, example 09:15-18:45, 11:30-21:00.....etc, the result return only 09:15-21:00

sorry for my english presentation

thank you very much for your guidance