| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting condition after the first duplicate
I have a spreadshhet that looks up stocks and the information that I want for
that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
thanks for such the timly reply..
i tried the first code, and it deletes everything but the first line(realestate) and the second line. this is what i start with: US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK and the result I am looking to get is something like this: US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK and if there is a way to keep the formatting of the cells i.e. fill color that would be a dded bonus.... thanks again for the help "Don Guillett" wrote: try this. Sub deletecattitles() mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5 'MsgBox Cells(i, mc) If Cells(i + 5, mc) = Cells(i, mc) Then Rows(i + 8).Delete Rows(i + 7).Delete Rows(i + 4).Resize(2).Delete End If Next i End Sub OR to JUST delete the second title and blank row Sub deletecattitles() [cpyblk].Copy Range("a1") mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5 'MsgBox Cells(i, mc) If Cells(i + 5, mc) = Cells(i, mc) Then 'Rows(i + 8).Delete 'Rows(i + 7).Delete Rows(i + 4).Resize(2).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "daphoenix" wrote in message ... I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
Give this subroutine a try...
Sub DeleteMultiples(ItemText As String) Dim X As Long Dim ItemRow As Long Dim LastRow As Long Const ColumnToProcess = "A" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _ Find(ItemText, After:=Cells(LastRow, "A"), _ LookIn:=xlValues).Row For X = LastRow To ItemRow + 1 Step -1 If Cells(X, ColumnToProcess).Value = ItemText Then Cells(X, ColumnToProcess).EntireRow.Delete End If Next End With End Sub You would call it from your own macro like this... Sub MyMacro() DeleteMultiples "US OE Specialty-Real Estate" End Sub Rick "daphoenix" wrote in message ... I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
You SHOULD have been able to THINK this out yourself. Don't know about the
formatting I can't see. BTW, I was a series 7 license holder when I was a Regional Manager for ING. Sub deletecattitles() mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5 'MsgBox Cells(i, mc) If Cells(i + 5, mc) = Cells(i, mc) Then Rows(i + 5).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "daphoenix" wrote in message ... thanks for such the timly reply.. i tried the first code, and it deletes everything but the first line(realestate) and the second line. this is what i start with: US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK and the result I am looking to get is something like this: US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK and if there is a way to keep the formatting of the cells i.e. fill color that would be a dded bonus.... thanks again for the help "Don Guillett" wrote: try this. Sub deletecattitles() mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5 'MsgBox Cells(i, mc) If Cells(i + 5, mc) = Cells(i, mc) Then Rows(i + 8).Delete Rows(i + 7).Delete Rows(i + 4).Resize(2).Delete End If Next i End Sub OR to JUST delete the second title and blank row Sub deletecattitles() [cpyblk].Copy Range("a1") mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5 'MsgBox Cells(i, mc) If Cells(i + 5, mc) = Cells(i, mc) Then 'Rows(i + 8).Delete 'Rows(i + 7).Delete Rows(i + 4).Resize(2).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "daphoenix" wrote in message ... I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
i cant seem to get this routine to work when calling MyMacro
"daphoenix" wrote: I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
ok i get an error saying object variable or with block variable not set
what does this exactly mean "daphoenix" wrote: I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
Who did you write this message to... Don or me? Either way, it would be a
good idea to post your MyMacro code so we can see what you tried to do. Also, it would help if you described what didn't work... Did you get an error message? Did nothing happen? Did something happen that you didn't expect? Rick "daphoenix" wrote in message ... i cant seem to get this routine to work when calling MyMacro "daphoenix" wrote: I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
rick, thanks for the help
i am getting runtime error 91 with this for the item row section : Sub MyMacro() DeleteMultiples "US OE Specialty-Real Estate" End Sub Sub DeleteMultiples(ItemText As String) Dim X As Long Dim ItemRow As Long Dim LastRow As Long Const ColumnToProcess = "B" With Worksheets("Psummary") LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _ Find(ItemText, After:=Cells(LastRow, "B"), _ LookIn:=xlValues).Row For X = LastRow To ItemRow + 1 Step -1 If Cells(X, ColumnToProcess).Value = ItemText Then Cells(X, ColumnToProcess).EntireRow.Delete End If Next End With End Sub "daphoenix" wrote: ok i get an error saying object variable or with block variable not set what does this exactly mean "daphoenix" wrote: I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
deleting condition after the first duplicate
It would be better if you would post your response in the sub-thread you are
responding to rather than start a new sub-thread every time you post a response. Okay, I just looked through my code and noticed I left out some "dots" that are necessary to force the references back to the worksheet referenced in the With statement. I also see that I left in a hard-coded column reference instead of using the ColumnToProcess name defined in the Const statement (you caught that when you substituted your "B" for my hard-coded "A" in the Cells function call). Below is revised code to correct those errors. See if it solves your problem. Sub DeleteMultiples(ItemText As String) Dim X As Long Dim ItemRow As Long Dim LastRow As Long Const ColumnToProcess = "B" With Worksheets("Psummary") LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _ Find(ItemText, After:=.Cells(LastRow, ColumnToProcess), _ LookIn:=xlValues).Row For X = LastRow To ItemRow + 1 Step -1 If .Cells(X, ColumnToProcess).Value = ItemText Then .Cells(X, ColumnToProcess).EntireRow.Delete End If Next End With End Sub Rick "daphoenix" wrote in message ... rick, thanks for the help i am getting runtime error 91 with this for the item row section : Sub MyMacro() DeleteMultiples "US OE Specialty-Real Estate" End Sub Sub DeleteMultiples(ItemText As String) Dim X As Long Dim ItemRow As Long Dim LastRow As Long Const ColumnToProcess = "B" With Worksheets("Psummary") LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _ Find(ItemText, After:=Cells(LastRow, "B"), _ LookIn:=xlValues).Row For X = LastRow To ItemRow + 1 Step -1 If Cells(X, ColumnToProcess).Value = ItemText Then Cells(X, ColumnToProcess).EntireRow.Delete End If Next End With End Sub "daphoenix" wrote: ok i get an error saying object variable or with block variable not set what does this exactly mean "daphoenix" wrote: I have a spreadshhet that looks up stocks and the information that I want for that stock. I am trying to create a grouping. Each stock has a category and when it is imported it is labeled with that category. What I am looking to do is if there is a way to delate the category name after the forst occurence looked up. So in the example below after the first US OE Speacilty-RealEstate have excel delete just the next occurence heading but leave the other information that follows. So it is like US OE Speacilty Real Estate is a group of funds. US OE Specialty-Real Estate SUSIX JPMORGAN U.S. REAL ESTATE A DJ WILSHIRE REIT TR MORNINGSTAR RANK US OE Specialty-Real Estate MUSDX MORGAN STANLEY INST US REAL ESTATE P DJ Wilshire REIT TR MORNINGSTAR RANK US OE Foreign Large Value CIVVX CAUSEWAY INTERNATIONAL VALUE INV MSCI EAFE NR USD MORNINGSTAR RANK US OE Mid-Cap Value MLUSX MASSMUTUAL SELECT MID-CAP VALUE S RUSSELL MID CAP VALUE TR MORNINGSTAR RANK |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting duplicate records | Excel Discussion (Misc queries) | |||
| Deleting duplicate row | Excel Discussion (Misc queries) | |||
| deleting duplicate rows | Excel Discussion (Misc queries) | |||
| Deleting duplicate records | Excel Discussion (Misc queries) | |||
| deleting duplicate names | Excel Worksheet Functions | |||
Linear Mode
