text match?
 

Hello all,
I'm using the following to copy weekly sales quote info
to a running master list:

Sub quotestomaster()

Set Rng1 = Workbooks("JeffQuotes_weekly.xls"). _
Sheets("Sheet1").Range("B3:R22")
Set Rng2 = Workbooks("JeffActiveQuotes.xls"). _
Sheets("Sheet1").Range("A" & Rows.Count).End
(xlUp).Offset(1, 0)
Rng1.Copy Rng2
End Sub

Since each job name is unique (appears in column B of
weekly file, column A in master) I want to ensure that no
duplications occur on the master. So, I need to see if a
match exists and if so, either don't copy it in the first
place or delete the match after copying. Wondering if I
can do something (in theory) in the master like...

=IF(TEXT(A1,"abcdefghijklmnopqrstuvwxyz")
="abcdefghijklmnopqrstuvwxyz" Then
EntireRow.Delete

Thanks in advance for response!
Erin

text match?
 

Erin ,

Try this formula

=IF(COUNTIF($A$1:$A$100,A1)1, "Duplicate","")

put it in B1 on the Master and copy down, and it will highlight the
duplicates.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"erin" <espencer@wdprosdotcom wrote in message
...
Hello all,
I'm using the following to copy weekly sales quote info
to a running master list:

Sub quotestomaster()

Set Rng1 = Workbooks("JeffQuotes_weekly.xls"). _
Sheets("Sheet1").Range("B3:R22")
Set Rng2 = Workbooks("JeffActiveQuotes.xls"). _
Sheets("Sheet1").Range("A" & Rows.Count).End
(xlUp).Offset(1, 0)
Rng1.Copy Rng2
End Sub

Since each job name is unique (appears in column B of
weekly file, column A in master) I want to ensure that no
duplications occur on the master. So, I need to see if a
match exists and if so, either don't copy it in the first
place or delete the match after copying. Wondering if I
can do something (in theory) in the master like...

=IF(TEXT(A1,"abcdefghijklmnopqrstuvwxyz")
="abcdefghijklmnopqrstuvwxyz" Then
EntireRow.Delete

Thanks in advance for response!
Erin

more info - text match?
 

Erin,

Try this then

Sub DeleteDuplicates()
Dim cLastRow As Long

With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1").FormulaR1C1 = "=IF(COUNTIF(R2C1:RC1,RC[-1])1,
""Duplicate"","""")"
.Range("B1").AutoFill Destination:=.Range("B1", .Cells(cLastRow,
"B")), Type:=xlFillDefault
.Range("A1").EntireRow.Insert
.Range("B1").FormulaR1C1 = "Test"
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Duplicate"
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"erin" <espencer@wdprosdotcom wrote in message
...
Hi,
Thanks for the response Bob.

I wanted to clarify... if I go with the idea of deleting
duplicates from the master, then after these are
identified, I need to *automatically* delete them.

-----Original Message-----
Hello all,
I'm using the following to copy weekly sales quote info
to a running master list:

Sub quotestomaster()

Set Rng1 = Workbooks("JeffQuotes_weekly.xls"). _
Sheets("Sheet1").Range("B3:R22")
Set Rng2 = Workbooks("JeffActiveQuotes.xls"). _
Sheets("Sheet1").Range("A" & Rows.Count).End
(xlUp).Offset(1, 0)
Rng1.Copy Rng2
End Sub

Since each job name is unique (appears in column B of
weekly file, column A in master) I want to ensure that
no
duplications occur on the master. So, I need to see if
a
match exists and if so, either don't copy it in the
first
place or delete the match after copying. Wondering if I
can do something (in theory) in the master like...

=IF(TEXT(A1,"abcdefghijklmnopqrstuvwxyz")
="abcdefghijklmnopqrstuvwxyz" Then
EntireRow.Delete

Thanks in advance for response!
Erin
.

more info - text match?
 

Beautiful! Thanks much :-)

-----Original Message-----
Erin,

Try this then

Sub DeleteDuplicates()
Dim cLastRow As Long

With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1").FormulaR1C1 = "=IF(COUNTIF
(R2C1:RC1,RC[-1])1,
""Duplicate"","""")"
.Range("B1").AutoFill Destination:=.Range
("B1", .Cells(cLastRow,
"B")), Type:=xlFillDefault
.Range("A1").EntireRow.Insert
.Range("B1").FormulaR1C1 = "Test"
.Columns("B:B").AutoFilter Field:=1,
Criteria1:="Duplicate"
.Cells.SpecialCells
(xlCellTypeVisible).EntireRow.Delete
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"erin" <espencer@wdprosdotcom wrote in message
...
Hi,
Thanks for the response Bob.

I wanted to clarify... if I go with the idea of
deleting
duplicates from the master, then after these are
identified, I need to *automatically* delete them.

-----Original Message-----
Hello all,
I'm using the following to copy weekly sales quote
info
to a running master list:

Sub quotestomaster()

Set Rng1 = Workbooks("JeffQuotes_weekly.xls"). _
Sheets("Sheet1").Range("B3:R22")
Set Rng2 = Workbooks("JeffActiveQuotes.xls"). _
Sheets("Sheet1").Range("A" & Rows.Count).End
(xlUp).Offset(1, 0)
Rng1.Copy Rng2
End Sub

Since each job name is unique (appears in column B of
weekly file, column A in master) I want to ensure that
no
duplications occur on the master. So, I need to see
if
a
match exists and if so, either don't copy it in the
first
place or delete the match after copying. Wondering
if I
can do something (in theory) in the master like...

=IF(TEXT(A1,"abcdefghijklmnopqrstuvwxyz")
="abcdefghijklmnopqrstuvwxyz" Then
EntireRow.Delete

Thanks in advance for response!
Erin
.



.