Posts by Jaffey

I found the following formula on another site that deletes all the text before the last colon (it deletes the colon as well)

=TRIM(RIGHT(M2,LEN(M2)-SEARCH("@#",SUBSTITUTE(M2,":","@#",LEN(M2)-LEN(SUBSTITUTE(M2,":",""))))))

Is it possible to modify it so that it only triggers if there is nothing after the last colon?

As you can see in the example below I have some notes entered by users and I would like to delete the ones where they didn't actually enter a note after the last colon:

[TABLE="width: 735"]

11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm

10/18/2012 08:03:55 NEW HOSSEN : called no answer

10/15/2012 11:15:41 NEW HOSSEN :

10/12/2012 08:00:26 NEW HOSSEN :

10/11/2012 12:17:14 NEW HOSSEN : called no answer

11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330

I would like to be left with just this instead:

[TABLE="width: 735"]

11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm

10/18/2012 08:03:55 NEW HOSSEN : called no answer
[TABLE="width: 735"]

10/11/2012 12:17:14 NEW HOSSEN : called no answer

11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330

[/TABLE]

[/TABLE]

Link to article I found this formula in: http://www.mrexcel.com/forum/e…particular-character.html

[/TABLE]

Re: modify Advanced Filter code to specify criteria range on different worksheet

That did the trick. Thank you so much.

I am trying to modify some code I found in another post to filter a list and then delete certain rows based on values in a range from a different worksheet instead of the current sheet. Here is the original code from the other post:

Sub Filter_Delete()          
Dim lrow As Long
lrow = Cells(Rows.Count, 2).End(xlUp).Row


With Sheet1         
.Range("B5:B" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _         
.Range("K1:K2"), Unique:= False
.Range("B6:B" & lrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete         
.ShowAllData     
End With     
End Sub

As I understand it the line that specifies the range containing the criteria to be used for deleting the rows is:

.Range("K1:K2"), Unique:=False

so I would like to change it to something like:

.Sheets("RowDeleteCriteria").Range("A2:A100")

Any help is much appreciated!

Here a link to the original post: (http://www.ozgrid.com/forum/sh…ete+rows+based+criteriato)

Re: insert new row for each line of text contained in a single cell and copy account#

Thank you for you reply. The correct file was attached. The sample data I referenced above was simply a minimized version of the real data in the spreadsheet but the columns, spacing etc are all the same. The only difference is that the attached spreadsheet did not show a "before" and "after" view. I have now fixed that and reattached the file. Thank you for looking into it.forum.ozgrid.com/index.php?attachment/49492/

I have a spreadsheet with a large # of rows. Individual (unique) account numbers are in column A and multiple lines of notes pertaining to the accounts are in column AW (column 49)
The typical note in a cell in column AW will contain between 5-20 lines of text separated by carriage returns Chr(13) + new line Chr(10).


What I would like to do is insert a new row in the spreadsheet for each line of text in the note in column AQ while repeating/copying the account# to the new rows.


Two rows of current data looks like:forum.ozgrid.com/index.php?attachment/49491/


Column A ......Column AW
#12345678 1234 Main St
..........789 High St.
..........147 Simon St
#9876543..2321 Shuster St.
..........246 Bow Street
..........1111 Hunter Lane
Note: the note lines (addresses) in the examples are in a single cell (Column AW)


Desired output looks like:


Column A .....Column AW
#12345678 1234 Main st
#12345678 789 High St.
#98765432 147 Simon St
#98765432 321 Shuster St.
#98765432 246 Bow Street
#98765432 1111 Hunter Lane

I would greatly appreciate any assistance in getting this done. I have attached a sample spreadsheet as well.

just wondering if any non Excel spreadsheet apps can run VBA/macros. I found one (Openoffice) that runs something similar and can even convert excel macros to their format albeit in a rather limited fashion. If anyone here knows of any others I'd love to hear about them. Thanks.

Re: delete X rows after every blank row not including the first non-blank row

Worked like a charm. Thank you jindon!!

I apologize in advance if my thread title sounds a bit nuts but please bear with me. I think you may agree it applies even if it does sound weird.

I ran a dupe file locator program across some drives and came up with 1000's of dupes. Below is a sample of the report showing the dupes which I dumped in excel. The dupe checker program will delete all the dupes leaving just the first instance of each file shown in the groupings below. What I would like is to take this report and delete all the extra rows of file names the same way the dupe checker is going to delete the files.

Once that is done I would like to move all the files to a single directory c:\sort for further sorting. This last step is considerably different than the first part of the problem so I'll understand if it needs to be posted later in it's own thread but if someone is up to the task I say thank you very much! Happy Friday to all.

File sample:

F:\Transcom\Lance\Business Plan\NCS Strategic Planning\10 POINT STRATEGIC PLANNING.pdf
F:\Transcom\Lance\Business Plan\NCS Strategic Planning\NCS Strategic Planning 2\10 POINT STRATEGIC PLANNING.pdf [<<delete this row]

E:\C drive\Documents and Settings\HP_Administrator.YOUR-4DACD0EA75\My Documents\Business\GOP\data source files\10,000 - 416's only less 1st 500.xls
F:\!My Docs backup\Business\GOP\data source files\10,000 - 416's only less 1st 500.xls [<<delete this row]
F:\old drives\1\My Documents\GOP\data source files\10,000 - 416's only less 1st 500.xls [<<delete this row]
F:\old drives\2\GOP3\data source files\10,000 - 416's only less 1st 500.xls [<<delete this row]
F:\Personal\GOP2\GOP\data source files\10,000 - 416's only less 1st 500.xls [<<delete this row]

E:\C drive\Documents and Settings\HP_Administrator.YOUR-4DACD0EA75\My Documents\condo dev lttr.doc
F:\!My Docs backup\condo dev lttr.doc [<<delete this row]
F:\Personal\condo dev lttr.doc [<<delete this row]

E:\C drive\Documents and Settings\HP_Administrator.YOUR-4DACD0EA75\My Documents\!Consulting\Consulting for Reliance\Confidentiality Agreement - Standard.doc
E:\C drive\Documents and Settings\HP_Administrator.YOUR-4DACD0EA75\My Documents\Legal\Confidentiality Agreement - Standard.doc [<<delete this row]
F:\!My Docs backup\!Consulting\Consulting for Reliance\Confidentiality Agreement - Standard.doc [<<delete this row]
F:\!My Docs backup\Legal\Confidentiality Agreement - Standard.doc [<<delete this row]
F:\deletable backups\!Consulting\Consulting for Reliance\Confidentiality Agreement - Standard.doc [<<delete this row]

F:\!!! TO USE AT CSC\CONFIDENTIALITY AGREEMENT feb1.doc
F:\LEGAL\CONFIDENTIALITY AGREEMENT feb1.doc [<<delete this row]

F:\!!! TO USE AT CSC\CONFIDENTIALITY AGREEMENT FOR SUPPLIERS.doc
F:\LEGAL\CONFIDENTIALITY AGREEMENT FOR SUPPLIERS.doc [<<delete this row]

Re: combine 1000's of csv files and append Lic#, filename and date to each row

Hi Jindon, that worked perfectly! Thank you so much!! This will be useful in so many ways. All the best to you!!

If it's not asking too much, could you please give me a brief description of what the regexp expression is doing? I have been trying to figure it using some regexp boards I found but so far no luck.

Thanks again!

Re: combine 1000's of csv files and append Lic#, filename and date to each row

Quote from jindon;608453

It is unclear which part is the name/Lic#/Lic type.etc.
Can you upload your desired result from your data provided?

Hi Jindon, thank you for taking an interest.

The layout of the CSV files, after the first row, is;

Surname, FirstName(s) Licence# LicenceType

Ex. Bingham , Martin John 40288241 Sales Agent

The desired output is

Surname, FirstName(s) Licence# LicenceType CompanyLic# Company Name Date

Ex. Bingham , Martin John 40288241 Sales Agent 40259395 ABC Corp 01/01/2012

I have attached an example to illustrate as well. Thank you.

I have approx 15,000 csv files that I need to combine into a single csv file (or worksheet)

The filenames look like this:
40259395_Consumer Portfolio Services Inc _Displaying records 1 through 10 of 37_051012.csv
40259395_Consumer Portfolio Services Inc _Displaying records 11 through 20 of 37_051012.csv
40259395_Consumer Portfolio Services Inc _Displaying records 21 through 30 of 37_051012.csv

The csv file contents contain a header row followed by a maximum of 10 data rows like this;

Displaying records 1 through 10 of 37 Next 1020304050Full NameLicense NumberLicense Type
Ayuyao , Ma.Micaela Irene Bennette 40276870 Sales Agent
Azul , Carlo Magno II 40175001 Debt Collector
Bingham , Martin 40288241 Sales Agent
Bruce , Carol
Burgins , Colin 40288215 Sales Agent
Burgins , Imelda Michelle Grace 40283925 Sales Agent
Coker , Marlon 40288270 Sales Agent
Coleman , Lona 20402350 Sales Agent
Collins, Jr. , William 40166027 Sales Agent
Cox , Ta'shalanda 40288245 Sales Agent

I would like to combine the csv files while appending the licence# and name of the company contained in the filename along with date contained at the end of the filename to each record.
I have tried using Ron DeBruin’s code (http://www.rondebruin.nl/csv.htm) for combining csv files into an xls file, however, it has trouble with the header row in each file and prepends it to the last record in the previous file. Also, from what I can see his code is appending each file as whole using the windows file copy so appending the company name and date to each row is not an option. I have viewed many posts on combining files but nothing quite like this.

Misc
99.9% of the rows/records contain only 1 comma and no other punctuation.
99.9% of the rows/records contain a name, lic# and type – if the lic#/type is missing, the row can be ignored or dropped
Appending the company name is not critical as long as the company lic# is appended.

If this is too big a project I’ll understand, but if that is the case if someone could please tell me how to delete the first row in every csv file that would be a huge help. If it could delete the first row AND insert the file name that would be even better as I could then run Ron DeBruin's code on the resulting files and then use some basic excel formulas to grab the details I need to populate the rows. Thanks!

Re: Bring down an equation to the last value of another column

The two easiest ways I know are :

with the first cell selected in the target column with the formula already in it;
copy the cell and then hold down the Shift key and press END and then HOME.
This will select everything below and/or down and to the right of your cursor.
You should be able to just paste after that, however, if more columns were selected than you intended, just press the left arrow key while still keeping the shift key pressed.

The other method is to double click the little square in the lower right corner of the selected cell but that is hit or miss because any data gaps in the adjacent column will stop it from copying all the way down

hope this helps

Re: Hide Pivot Items by Keyword

That worked. Thank you!

Re: Hide Pivot Items by Keyword

I'll try that but it looks like it's only going to hide items with that exact name....I want to hide any row containing that keyword appearing in the name/pivot item

I'm trying to hide pivot items containg a keyword. For example, if the pivot item contains the word 'CREDIT', I want to hide/deselect that row. I think the following code is close but it's not working. Any help is appreciated. (currently it cycles through all the pivot items but doesn't hide any of the rows where the word credit appears)

Sub HidePivotItems()
    Dim pt As PivotTable, pi As PivotItem
    Set pt = ActiveSheet.PivotTables("PivotTable1")
          
    For Each pi In pt.PivotFields("Agency").PivotItems
        pi.Visible = Not pi.Name Like "CREDIT"
      
    Next pi
End Sub

Re: remove specific text and dates within a cell

That works great. Thank you very much.

Re: remove specific text and dates within a cell

Thanks for the suggestion Jindon. I tried to use that by modifying some of my current code but I couldn't get it to work. Basically I am using statements like:

s = Cells(row, 6).Value
Cells(row, 6).Value = Replace(s, " Agent Create Event", "")

to remove the redundant phrases but I was unable to get it to work using ??/??/???? to remove the dates as you suggest.

Re: remove specific text and dates within a cell

Perhaps I can simplify my request by simply asking; how can I remove the dates from a long string of text in a single cell containing multiple dates and snippets of text and where the dates are in the format "mm/dd/yyyy". eg. 05/26/2010

I am trying to remove selected strings of text and the date associated with them from a large number of customer account records.

For example, I have the following text in a single cell;

09/02/2011 DRNAgencyProcess CLOSE NB1X1691 5 ACMCLS RECALLED BY CLIENT 08/20/2010 Agent Forced to close case 08/13/2010 Agent no acct to transfer, no unposted payment,called customer thru number 780-691-3164 as per recording number is not recognized mailbox.updated to 111-1111.no other number listed.future credit events already in place./u2ds 06/12/2010 Agent Event Status Date Changed to 2010-06-11. Event Status Changed to Completed . 06/12/2010 Agent Create Event

Many of the comments are system generated and/or repetitive and I am trying to strip them out. To look at the comments individually they look like this;

09/02/2011 DRNAgencyProcess CLOSE NB1X1691 5 ACMCLS RECALLED BY CLIENT
08/20/2010 Agent Forced to close case
08/13/2010 Agent no acct to transfer, no unposted payment,called customer thru number xxx-xxx-3164 as per recording number is not recognized mailbox.updated to 111-1111.no other number listed.future credit events already in place./u2ds
06/12/2010 Agent Event Status Date Changed to 2010-06-11. Event Status Changed to Completed
06/12/2010 Agent Create Event

The only comment I want to keep from the above example is the one starting with "08/13/2010 Agent no acct to transfer...". The rest I want to delete. I am currently using Replace to remove repetitive comments like "Agent Create Event" but I can't figure out how to remove the date associated with it so I end up with a bunch of dates with no text between most of them. I tried modifying the code below from another post but could not get it to work.

Sub ReplaceDate(r As Range, da1 As Date, da2 As Date)
    Dim cell    As Range
     
    For Each cell In r
        With cell
            If IsDate(.Value) And Not .HasFormula Then
                If Int(.Value) = da1 Then
                    .Value = .Value - da1 + da2
                End If
            End If
        End With
    Next cell
End Sub

I have a directory with a hundred or so csv files to import. Some rows/records are duplicated in each file.
My goal is to combine all the files on one worksheet while removing the duplicate rows.
I have two macros I found in other Oz posts that now import the csv files into worksheets and then combine them into one worksheet while removing the dupes but it runs progressively slower and slower as the number of records increases until it essentially hangs completely.

I was hoping someone could look at the attached macros and perhaps suggest some enhancements/fixes.

One obvious fix would be to not remove the dupes after each worksheet is copied but if I try to add all the files first, the 65K row limit gets in the way so my only option was to run the dupe remover as each worksheet is copied over since my VB skills are insufficient to make it only remove dupes when the row limit is going to be surpassed.

Sub Copy_Sheets_To_DataSheet_RemoveDupes()
  Dim shtName As Worksheet
  Dim lstRow As Long
   
  For Each shtName In Worksheets
  If Left(shtName.Name, 14) = "CollectionTool" Then
      shtName.Select
      Range(Selection, ActiveCell.SpecialCells(xlCellTypeLastCell)).Copy
      Worksheets("Data").Select
      Range("A1").Select
      Selection.End(xlDown).Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   End If
   'remove dupes
    Application.Run "DeleteDuplicates"
   Next
End Sub

Public Sub DeleteDuplicates()
    Sheets("Data").Select
    Dim StartRow As Long, CheckRG As Range, ColToCheck As Range
    Dim UniqCells() As String, UniqCount As Long, i As Long, DelRG As Range
     'Set row to start searching for duplicates
    StartRow = 2
     'Determine the range of used cells
    Set CheckRG = Intersect(Rows(StartRow & ":65536"), ActiveSheet.UsedRange)
    If CheckRG Is Nothing Then Exit Sub 'Nothing to check
     'Ask the user what column should be tested for duplicate values
    Do
        Set ColToCheck = Range("c:c")
 'Application.InputBox(prompt:="Which column should be" & vbNewLine & "checked for duplicates?", Title:="Please select single column", Type:=8)
 '*****You can remove the "Range("c:c") and uncomment the above line. This asks the user to select the column where duplicate values are to be checked
    Loop Until ColToCheck.Columns.Count = 1
     'Begin the process of removing duplicates by loading unique values into string array
     ' and setting the duplicate rows into a range to delete later on
    UniqCount = 0
    ReDim UniqCells(0)
    For i = 1 To CheckRG.Rows.Count
        If Not InSArray(UniqCells, Intersect(CheckRG.Rows(i).EntireRow, ColToCheck).Text) Then
            ReDim Preserve UniqCells(UniqCount)
            UniqCells(UniqCount) = Intersect(CheckRG.Rows(i).EntireRow, ColToCheck).Text
            UniqCount = UniqCount + 1
        Else
            If DelRG Is Nothing Then
                Set DelRG = CheckRG.Rows(i).EntireRow
            Else
                Set DelRG = Union(DelRG, CheckRG.Rows(i).EntireRow)
            End If
        End If
    Next i
     
     'Delete duplicate rows
    If Not DelRG Is Nothing Then 'If there are duplicate cells...
        Application.ScreenUpdating = False 'Turn off screen updating
        DelRG.Delete 'Delete duplicate range
        Application.ScreenUpdating = True 'Turn on screen updating
    End If
     
End Sub

Function InSArray(ByRef vArray() As String, ByVal vValue As String) As Boolean
    Dim i As Long
    For i = LBound(vArray) To UBound(vArray)
        If vArray(i) = vValue Then
            InSArray = True
            Exit Function
        End If
    Next i
    InSArray = False
End Function