Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match text | Excel Worksheet Functions | |||
Match a text | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
How to match two different cells text, if the text is not in the . | Excel Worksheet Functions | |||
match cell text with text in formula | Excel Worksheet Functions |