Posts by Jaffey

Re: Remove duplicate phone numbers on same row

Thank you so much Jindon. Unfortunately I am getting a type mismatch error on the following line when I use it on real data:

txt = Join(Application.Index(a, i, 0), Chr(2))

The data is in Excel table format. Would that make a difference? I tried converting it to a range but that didn't help.

When I try it with the test data I provided it's working but I noticed that with one of the phone numbers it truncated the area code of the firs occurrence of the number.

i.e. 905747899 in Cell H4 becomes 7477899 after I run it.

Re: Remove duplicate phone numbers on same row

Thank you for responding! Unfortunately I have to do this every day and there 10's of 1000's of rows to deal with in multiple list, plus there are many other columns of various customer data inserted between the phone number columns. Nice formula though, works very nicely

I have a table with a large number of customer ID's in column A and associated customer data in columns to the right.
10 to 15 columns will contain phone numbers. The problem is the same phone number will appear in more than one column.
I would like to delete the duplicates keeping just the first occurrence of the number. (closest to column A).
All of the columns have labels and columns containing phone numbers have "Phone" somewhere in the label. (the column order changes sometimes so I can't specify by column number)

Any help greatly appreciated!! (sample data attached)

forum.ozgrid.com/index.php?attachment/64842/

Re: Extract data elements from a large HTML text file

Hi Apo, I just tested it with approx. 1,000 live records and it worked perfectly! If I run into any problems running the larger data sets I'll let you know. Thank you so much for your solution.

Re: Extract data elements from a large HTML text file

I've added a screenshot of what the data looks like on the webpage as well as a copy of the same data in a text file, which is my source file for processing.

Each record is numbered beginning at "1" followed by a dash then the customer account number, then a colon, then a 1 to 3 digit number. (not required)
Ex 1-00123456:1
2-45145558888:49
The account # appears again later in the record prefaced by "Account # "
Ex "Account # 5177590000225777"

Generally speaking, each record begins the account number, prefaced by the record# as indicated above (1-00123456:1), and ends with the second instance of the account that looks like ""Account # 00123456:". Note: some records have 2 addresses, the second of which will appear after the second appearance of the account number, which can make the end of the record appear confusing. Thanks again for helping.

Re: Extract data elements from a large HTML text dump into Excel

<editing title>

Re: Extract data elements from a large HTML text file

Hi Cytop, thank you for replying. There is no file unfortunately. I have to copy/paste the data from the site into excel. I will re-title my post "Extract data elements from a large HTML text dump". I tried copying the data into a text file actually but it looked even less workable to me. I could upload that tomorrow though, the system is not available at the moment.

I have a large text (html) file (500,000 rows) from which I have to extract certain elements. The file layout is fairly consistent and each element has a clear identifier. (sample attached) Unfortunately the source of the data is a webpage and the only option is to copy paste the data into excel (or Word, text file etc).

I need to extract the date, time and account# appearing to the right of the following labels/tags.

Example:
Diarized for: Wed Aug 13th,2014 @ 12:00AM
Last Viewed: Aug13th,[email protected]:28
Account# 452145871235

The extracted data should appear in 3 columns:

Account | Diarized Date | Viewed Date

Occasionally a record will have a viewed date of ‘Never’ and no Diarized date at all. Ie. not even the “Diarized for: “ label will appear. Also, records with long or multiple addresses will spread across multiple rows so the number of rows per record can vary.
Thank you for helping!

Re: Formula to calculate number of working days left in month

I think I solved it. =NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)) give the number of working days left in the month based on whatever today is. It doesn' take into account holidays however so if anyone could suggest how that could be done I would be very much in your debt! Thanks to everyone who replied.

Re: Formula to calculate number of working days left in month

I set the date on my PC ahead to Nov 15 and received the following results using the suggested formulas:

=EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(TODAY(),0),3)>5,-2,IF(WEEKDAY(EOMONTH(TODAY(),0),3)>4,-1,0)) = 14

=EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0)) = 15

=EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))=14

=EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0))=14

None of them are doing what I want. On Nov 15th there will be 9 actual business days left in the month. (10 business days minus Thanksgiving) These formulas appear to be giving me the number of calendar days left in the month which is not what I need. Similarly, when I set my PC to Sep1, I get results of 29 and 30 but with few exceptions there are never more than 22 business days in a month.

Re: Formula to calculate number of working days left in month

Thank you for looking at this. I tried your formula as is, and with today() inserted as you suggested:

=EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))

and both give a result of 3 but the result should be 1, assuming your formula is calculating the number of working days left in the month(?)

I have seen many posts on calculating the number of days between two dates and subtracting the number of holidays. Ex. =NETWORKDAYS(StartDate,EndDate) does that very nicely.

My question is, how do I determine what workday number today is so I can figure out how many workdays are left in the month.

I did come up with a formula that tells me how many calenadar days are left in the month:
=(EOMONTH(TODAY(),0)-TODAY()) but it doesn't tell me how many workdays are left and I have been unable to adapt it.

For example, today is Aug 29th. The first formula tells me there are 22 working days in Aug. while the 2nd formula tells me there are 2 days left in the month, but there is only one working day left in the month. I would like to accomplish this using only one cell as input, which is a hardcoded "Period ending date" which is typically yesterday. Thank you.

Re: Rename 500 Sheets based on Modified Cell Value on each sheet

That worked! Thank you!

I recieve a workbook each day with approx 500 tabs of call data and I would like to relabel all the tabs with the user names. The user names are in a merged range "A7:M7". I'm having difficulty getting the following script to rename the sheets. Currently the values in "A7:M7" look like "User: ADAM ENGEMANN-558".

I am trying to delete each occurrence of "User: " while naming the sheets as well as removing the merged range before doing so that the names are in A7 only. The sheet names should look like "ADAM ENGEMANN-558", or, ideally just "ADAM ENGEMANN". For some reason my code will not modify the cells and I can't figure out why. It's really driving me crazy. Any help much appreciated! (see attached example)

This works if I manually unmerge the cells before running and remove the ":".

Sub RenameSheets2()
    Dim ws As Worksheet
     
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("A7").Value <> "" Then
    
            ws.Name = ws.Range("A7").Value            


        End If
    Next ws
     
End Sub

This his how I modified it but it doesn't work on the supplied sheets. Please note that if I manually insert some blank sheets into my workbook and merge the cells etc it does work. There seems to be something odd going on with the sheets they are giving me. (they aren't protected)

Sub RenameSheets2()
    Dim ws As Worksheet
     
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("A7").Value <> "" Then
         
    'I tried adding the following to remove the merged ranges and remove 'User: ' from the names
    Range("A7:M7").Select
    With Selection
        .WrapText = False
        .MergeCells = False
    End With
    Range("A7").Select
    Selection.Replace What:="User: ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                
            On Error Resume Next
            ws.Name = ws.Range("A7").Value
            
        End If
    Next ws
     
End Sub

forum.ozgrid.com/index.php?attachment/55428/

Re: transpose rows to combination of rows and columns

It's working perfectly now. Thank you so much.

Re: transpose rows to combination of rows and columns

Treat them like the other rows if possible. i.e. Ignore the equals sign and move the text where it belongs based on the Other_# value.

Re: transpose rows to combination of rows and columns

forum.ozgrid.com/index.php?attachment/53442/
Here's an example of notes it's catching on. The notes were entered with an equals sign as the first char and excel is treating them as invalid formulas. I tried formatting them as text but it made no difference.

Re: transpose rows to combination of rows and columns

That fixed 99% of the issues. The only snag now is it halts when the cell contains #VALUE! (residue from some pre-processing before I receive the file)

Re: transpose rows to combination of rows and columns

forum.ozgrid.com/index.php?attachment/53411/ On my machine if I delete row 10 it works

Re: transpose rows to combination of rows and columns

Also, I notied about 100 entries where the user had entered "=========" and nothing else. It stops on all of those.