Transpose Function..Empty Cells and Characters

[chrisscotty]

Hello All,

I just recently switched to Excel 2007 and am not that familiar with the program.

I have a question that has 3 components.


I have alot of data in Column A that has many blank cells.

I performed several searches and have still not been able to remove them.

This particular method worked if I was selecting a small amount of data

http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/

Yet when I use with the entire list I get the error "selection is too large".


What I basically want to do is take the data that I have gathered and transpose it so I can save as a csv file and import in to my contact manager.


The data looks like this:
NEXT ENTRY
" "




Mr. Rene Reynolds

Chapters: Philadelphia
Position/Title: Machinist
Company: ABC Limited

71 Maybrook Drive
Philadelphia, PA 19118, USA
Bus. Telephone: (215) 298-8108x265
Fax: (215) 298-3545
E-mail: rene.reynolds@abc.com
NEXT ENTRY
" "






Ms Carol Jones

Chapters: Ottawa
Position/Title: Benefits Administrator

28 Reeds Road
North Bay, ON K9J 0K4, CANADA
E-mail: c_jones@yahoo.com
NEXT ENTRY


Mr Billl Jones

Chapters: Binghampton
NEXT ENTRY

----------

So each name after "Next ENTRY" is a new entry and I want the data transposed

Now, all this is consistent as far as the order..name, chapter, postion etc.

The thing is not all names have all the information some just have the name and phone, name and chapter etc etc.

Mr. Rene Reynolds

Chapters: Philadelphia
Position/Title: Machinist
Company: ABC Limited

71 Maybrook Drive
Philadelphia, PA 19118, USA
Bus. Telephone: (215) 298-8108x265
Fax: (215) 298-3545
E-mail: rene.reynolds@abc.com

So..first I want to get rid of all the empty cells and move up and then transpose the data and have it correspond to each entry and if it missing a fied it just skips over.

Oh and one more thing =-)

There appears a little question mark that I can not delete. Some of them appear before the name and some of them appear under NEXT ENTRY and appear as " " when I paste data from excel in to a txt file.

Any help would be greatly appreciated.

 

[chrisscotty]

This is a brief explanation;

1) Read all the text from a Text file

..........Yes
2) Split 1) into each line
.........Not sure what you mean..
3) Each time "NEXT ENTRY" was find, add new row in the result and put next none blank row to Name field.

........Yes
4) if the line incl. ":", like something1:something2, something1 becomes a header and something2 will be actual data in corresponding columns header.

.....Yes
5) if not 4) then all the lines will be combined to "Address" field.

...Yes

 

[Legacy 68668]

May be the balnk lines are not blank...
How about

Sub test()
Dim fn As String, temp As String, n As Long, t As Long, flg As Boolean, a()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
fn = "c:\test\aaa.txt"    
temp = "NEXT ENTRY" & _
      CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
temp = CleanTxt(temp)
ReDim a(1 To Rows.Count, 1 To 20)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In Split(temp, vbCrLf)
        If Trim(e) <> "" Then
            If n > 0 Then
                x = Split(e, ":")
                If UBound(x) > 0 Then
                    If Not .exists(x(0)) Then
                        t = t + 1 : a(1, t) = x(0) : .add x(0), t
                    End If
                    a(n, .item(x(0))) = x(1)
                Else
                    If flg Then
                        a(n, 1) = e : flg = False
                    Else
                        If Not .exists("Address") Then
                            t = t + 1
                            .Add "Address", t
                        End If
                        a(n, .item("Address")) = a(n, .item("Address")) & _
                            IIf(a(n, .item("Address")) = "", "", " ") & e
                    End If
                End If
            End If
        End If
        If UCase(e) Like "*NEXT ENTRY*" Then
            n = n + 1 : flg = True
        End If
    Next
End With
ThisWorkbook.Sheets(1).Cells(1).Resize(n, t).Value = a
End Sub
 
Function CleanTxt(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "^\W*$"
    .Global = True
    .MultiLine = True
    CleanTxt = .replace(txt, "")
End With
End Function

 

[chrisscotty]

You are right...

Now it is missing all the names.

After NEXT ENTRY is the Name without any blanks (well not all the time but often)

There are blank spaces as well after some of the other fields.

So could be:


NEXT ENTRY





Ms. Bill. Jones


Chapters: San Diego

12 ABC
San Diego, CA,90234,USA




NEXT ENTRY
Ms. Jill ABC

Chapters: ETC
Position/Title: ETC
Company: ABC.

123 St.
Miami,FL.99999,USA
Bus. Telephone: (205) 888-5555
Fax: (768)567-8901
E-mail: jis@aa.com
NEXT ENTRY

 

[Legacy 68668]

Can you change and try ?

    .Pattern = "^\W*$"

to

    .Pattern = "^\s*$"

 

[chrisscotty]

What is interesting is that the names do not show up at all at this point.

The name is always on a separate line always first before address or chapter etc but does not have "Name:" header ,just has the name.

Also, there are still many entries where NEXT ENTRY is entered in the line where the address is...also it will have a corresponding email address from another entry.

 

[Legacy 68668]

If that's the case, didn't name appear in Address column ?

 

[chrisscotty]

No, they were before (not all..just at the beginning) but not now

 

[chrisscotty]

Also interesting to note is that the first header is "next entry" and then no header (which has address city etc) and then e-mail company bus telephone etc..


Also..many have "next entry" where the address goes and has the email address ...yet the next entry is correct (without name still though)

 

[Legacy 68668]

Can you just add one line

ReDim a(1 To Rows.Count, 1 To 20)
n = 1
With CreateObject("Scripting.Dictionary")

 

[chrisscotty]

Looks Perfect .

Thank You !

Is there any way of separating the name from the rest of the address?