text match?

[erin]

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

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

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]

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)

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.
if
a

.