Posts by Jaffey

    Re: transpose rows to combination of rows and columns

    It's working but it keeps crashing. I've attached screenshots of the errors. Please note some of the user notes may consist of nothing but an asterisk, or sometimes just a period, or a line "===", or any other character, or sometimes nothing.[ATTACH=CONFIG]53400[/ATTACH][ATTACH=CONFIG]53401[/ATTACH]

    I have a column of text strings of varying lengths from 1 to 512 chars. I would like to insert a comma every 79 chars. I found a function that inserts a comma every 2 chars so I thought it would be easy to convert it to every 79 chars but I can't figure it out. I keep ending up with a mess. Can anyone help me fix this?

    Function InsertComma(Text As String) As String
    If Len(Text) < 3 Then InsertComma = Text
    Dim i As Integer
    Dim T As String
    T = Left(Text, 2)
    For i = 3 To Len(Text) Step 2
    T = T & "," & Mid(Text, i, 2)
    Next i
    InsertComma = T
    End Function

    found here:

    This was my failed attempt at modifying it:

    Function InsertComma(Text As String) As String
    If Len(Text) < 80 Then InsertComma = Text
    Dim i As Integer
    Dim T As String
    T = Left(Text, 79)
    For i = 80 To Len(Text) Step 2
    T = T & "," & Mid(Text, i, 79)
    Next i
    InsertComma = T
    End Function

    I've reviewed numerous posts on transposing data but none of them so far deal with a situation quite like this. I've attached a picture that hopefully describes what I'm trying to accomplish better than I can but I'll try; I have 1000's or rows of client account data containing user notes, 1 row for each note. The notes are typically a few sentences in length. Many accounts have numerous notes therefore the same client#/Acct# can appear many times in the data. I need to transpose the data so that each acct# appears only once and the notes are transposed horizontally beside the acct# according to the sequence of the "Other_#" in the column preceding the note. I've tried to show an example below using text as well.


    89252 1 1000 Other_1 note1
    89252 1 1000 Other_2 note2
    89252 1 1001 Other_1 note1
    89252 1 1001 Other_2 note2
    89252 1 1001 Other_3 note3
    89237 1 1002 Other_1 note1
    89237 1 1002 Other_2 note2
    89237 1 1002 Other_3 note3
    89237 1 1002 Other_4 note4


    CL_NO ACCT# Other_1 Other_2 Other_3 Other_4 .....
    89252 1000 note1 note2
    89252 1001 note1 note2 note3
    89252 1002 note1 note2 note3 note4

    I am trying to modify krishna kumar's function (found here:…=If+SplitCount+Len%28v%29) to work with rows instead of columns. I have copied the code from his post below. As per his post it is accessed by using the formula =XTRACT40($B3,COLUMNS($C3:C3))

    I have tried changing it to =XTRACT40(B$3,ROW(C$3:C3)) and =XTRACT40(B$3,ROWS(C$3:C3)) (array) and several other variations with no success.

    To quickly summarize what krishna kumar's code is doing; it splits up long sentences/text strings contained in a single cell and spreads them over multiple cells to the right of the cell with the long sentence/text string. In the example below any sentence/text string over 40 chars is broken up into max 40 character segments per cell.

    For example, if you had "The quick brown fox leaped over the restless dog" in cell B3 and copied that formula into cells C3 and D3 you would end up with "The quick brown fox leaped over the" in cell C3 and "restless dog" in D3, even though ""The quick brown fox leaped over the" is only 35 chars long. This is because krisha kumar's code is also smart enough not to break up words, in this case 'restless' where the 't' is the 40th character.

    My goal is to be able to use this function by coping the formula into the cells beneath the target cell instead of beside them. Any assistance is greatly appreciated.

    Re: combine 24,000 text files and append filename details to each record

    The directory contains the .xlsm file containing my project and a few csv files I had changed to ".txt" while troubleshooting previously. In any case, the code runs great now and it is FAST :) Thank you so much!!

    I did notice upon examining the results that my data contains about 50 names (out of 100,000) with 2 commas instead of 1 so they don't align properly. I didn't see any of those in the sample files I was working with originally or I would have mentioned it. Is that something that can be adjusted for without too much difficulty? I realize that is a new question and may require a separate post. I can fix them manually in a few minutes so it's not that big a problem but this is a process I have to repeat on a regular basis so it would be nice if it could accommodate them as well. Thanks again for all your help!

    Burton Jr. , Anthony
    Cupp, Jr. , DonaldPerkins, Jr. , Gary

    Re: combine 24,000 text files and append filename details to each record

    If that's the same code as your previous post then yes. I just copied it on top of what I had. When I choose the Debug option after it stops it highlights the same line as before:

    [COLOR=#333333]c00 = c00 & vbLf & Replace(Join(Filter(Split(Replace(Replace(Replace(.opentextfile("C:\test4\" & sn(j)).readall, vbCrLf, "|") & "||||", [/COLOR][COLOR=blue]String[/COLOR][COLOR=#333333](4, "|"), ";" & Join(Filter(Filter(Split(sn(j), "_"), ".csv", [/COLOR][COLOR=blue]False[/COLOR][COLOR=#333333]), "Display", [/COLOR][COLOR=blue]False[/COLOR][COLOR=#333333]), ";") & "|"), [/COLOR][COLOR=blue]String[/COLOR][COLOR=#333333](2, "|"), ";"), "|"), ","), vbLf), ";", ",")

    I will try it again using F8 as you suggest and let you know what I see in the watch window. Tks[/COLOR]

    Re: combine 24,000 text files and append filename details to each record

    Thank you snb. You are right I specified txt files instead of csv. Sorry about that. I actually did notice that while I was trying to figure out why your code wasn't working and I had already tried changing ".txt" to ".csv" but still received the same error, however instead of appearing instantly it processed for about 20 seconds before popping up. I tried your modified code above just now and received the same 'Path not found' error after 20 seconds or so. Thanks again for looking at this.

    Re: combine 24,000 text files and append filename details to each record

    Thank you Jindon! I just did a quick test and your code worked great.

    Thank you for your reply as well snb. Unfortunately I get a 'path not found' error on

    [COLOR=#333333]c00 = c00 & vbLf & Replace(Join(Filter(Split(Replace(Replace(Replace(.opentextfile(ThisWorkbook.Path & "\" & sn(j)).readall, vbCrLf, "|") & "||||", [/COLOR][COLOR=blue]String[/COLOR][COLOR=#333333](4, "|"), ";" & Join(Filter(Filter(Split(sn(j), "_"), ".txt", [/COLOR][COLOR=blue]False[/COLOR][COLOR=#333333]), "Display", [/COLOR][COLOR=blue]False[/COLOR][COLOR=#333333]), ";") & "|"), [/COLOR][COLOR=blue]String[/COLOR][COLOR=#333333](2, "|"), ";"), "|"), ","), vbLf), ";", ",")

    . If it helps, both my spreadsheet and the text files are in c:\test4[/COLOR]

    I receive 24,000 text files once a month that need to be combined into one csv/txt file and/or spreadsheet(tab).

    About a year ago I posted a thread on the same topic which received a fantastic response from jindon that worked great( found here:…php?t=165341&goto=newpost**)

    Unfortunately, the format in which the text files are ouput has changed, as has the filename layout. The files are now output with filenames such as:
    (lic#, company name, displaying # records found, date, type.txt)

    40298827_Windham Professionals Inc _Displaying records 1 through 10 of 100_041813_AGENTS.txt
    40298827_Windham Professionals Inc _Displaying records 11 through 20 of 100_041813_AGENTS.txt

    (files with EANF in the filename have no records inside them and can be skipped)

    While the contents of each file look like this: (see attached text file reference)
    Displaying records 1 through 10 of 100

    Full Name
    License Number
    License Type

    Audette , Anthony
    Sales Provider

    Burritt , Kimberly
    Sales Provider

    I would like to combine the contents of the text files while appending the lic#, company name and date from the filenames to each record so the resulting file looks like this:

    40305196 Audette , Anthony Sales Provider 40298827 Windham Professionals Inc 041813
    40313800 Burritt , Kimberly Sales Provider 40298827 Windham Professionals Inc 041813

    As far as I can tell jindon's code is fine except the regex expression needs to be modified to handle the new layout, however that is far beyond me. If anyone can help me with this I would appreciate it very much as I have been spending many many hours combining these records manually. cheers!

    I have a simple macro that loops through a range of cells using the following;

    Dim row
    For row = 1 to 100
    next row

    My question is how can I modify that statement to exclude rows that are hidden? I have looked at many threads but I must not be using the right terms because I can't find anything that addresses this.

    Many thanks in advance!

    Re: Add 2 hours to every date/time result in a column of data

    I got a type mismatch error on

    [COLOR=#333333]ws.Cells(row, "A").Value = ws.Cells(row, "A").Value + TimeSerial(2, 0, 0)

    but managed to fix it when I remembered excel treats dates as numbers so I replaced "[/COLOR]TimeSerial(2, 0, 0)" with "0.08333333" but it took several minutes to run. While I was waiting I finally remembered you can add/subtract an amount from all the values in a column/row of data by first copying the value you want to add/subtract to your clipboard and then doing a paste special. So I recorded that and came up with the following.

    Sub Add2Hrs()
        Range("AF1").Value = "0.08333333"' = 2 hrs
        Range("F2").Select 'where the date/time values start
        Range(Selection, Selection.End(xlDown)).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=True 
    End Sub

    and it worked. Thanks again.

    I have column of date/time data as follows:

    02/01/2013 8:49

    I need to add two hours to every cell so that it looks like this:

    02/01/2013 10:49

    I know there must be an easy way to do this but I can't find it. All of my searches keep showing me how to add times, not add to the time. Any help greatly appreciated!