Posts by Jaffey

Jaffey · May 2nd 2013

Re: transpose rows to combination of rows and columns

I have eXCEL 2007. When I manually delete the rows with blanks, #N/A, asterisks, "=", etc. it runs fine and copies data over/across 600 columns. Ex. "OTHER_650"

Jaffey · May 1st 2013

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]

Jaffey · Apr 29th 2013

Re: modify UDF function to insert a comma every 79 chars instead of every 2 chars

That did the trick. Thank you!

Jaffey · Apr 29th 2013

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?

Code

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: http://www.mrexcel.com/archive/VBA/16676.html

This was my failed attempt at modifying it:

Code

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

Jaffey · Apr 29th 2013

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.
[ATTACH=CONFIG]53337[/ATTACH]

INPUT

CL_NO ACCT# OTHER NOTES
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

OUTPUT

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

Jaffey · Apr 28th 2013

Re: modify text splitting function to work with rows instead of columns

That works great. Thank you very much.

Jaffey · Apr 27th 2013

Re: modify text splitting function to work with rows instead of columns

thanks cytop

Jaffey · Apr 27th 2013

I am trying to modify krishna kumar's function (found here: http://www.ozgrid.com/forum/sh…=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.

Code

Function XTRACT40(ByRef v, Optional SplitCount As Long = 1) As String 
    Dim s As String, i As Long, n As Long 
    
    If SplitCount = 1 And Len(v) < 41 Then 
        XTRACT40 = v 
        Exit Function 
    Else 
        For i = 1 To Len(v) 
            n = n + 1 
            s = Mid$(v, i, 41) 
            Select Case Asc(Right$(s, 1)) 
            Case 32, 10, 44 
                XTRACT40 = Trim$(Left$(s, 40)) 
            Case Else 
                XTRACT40 = Trim$(Left$(s, InStrRev(s, " "))) 
            End Select 
            If n = SplitCount Then 
                XTRACT40 = Trim$(XTRACT40) 
                Exit For 
            Else 
                i = i + Len(XTRACT40) 
                XTRACT40 = vbNullString 
            End If 
        Next 
    End If 
End Function

Display More

Jaffey · Apr 26th 2013