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.

 

[Legacy 68668]

1) Is the original data in Col.A imported ? (csv txt etc?)
2) how do you want the result ?

 

[chrisscotty]

Yes it was imported from a text file.

I could just as easily copy and paste from the text file and/ or make sure the first name/entry is on the first row.

I want the data going across instead of down so I can import in to my contact manager.


So instead of :

A

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

I want it transposed to NAME CHAPTER ADDRESS BUS Telephone FAX E-mail

If the person only has :

Bill Smith

Chapters: New York
E-mail billsmith@gmail. com


then I want the other fields skipped


The term NEXT ENTRY separates all the names and information but there are alot of empty fields that I want to delete before running transpose.

Thanks for the quick response !!!

 

[Legacy 68668]

Let's see if this works.

Sub test()
Dim fn As String, temp As String, n As Long, t As Long
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
fn = "c:\test\test.txt"    '<- File path
temp = "NEXT ENTRY" & _
      CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In Split(temp, vbCrLf)
        If e <> "" Then
            If UCase(e) = "NEXT ENTRY" Then
                n = n + 1
            End If
            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 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
    Next
End With
ThisWorkbook.Sheets(1).Cells(1).Resize(n, t).Value = a
End Sub

 

[chrisscotty]

I get the error:

"sub or function not defined"


and in the macro


a(1, t)= is highlighted.

 

[Legacy 68668]

OOps

Sub test()
Dim fn As String, temp As String, n As Long, t As Long, a()
fn = "c:\test\test.txt"    '<- File path
temp = "NEXT ENTRY" & _
      CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
ReDim a(1 To 10000, 1 To 50)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In Split(temp, vbCrLf)
        If e <> "" Then
            If UCase(e) = "NEXT ENTRY" Then
                n = n + 1
            End If
            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 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
    Next
End With
ThisWorkbook.Sheets(1).Cells(1).Resize(n, t).Value = a
End Sub
<!-- / message -->

 

[chrisscotty]

Now I am getting

"input past end of file"

 

[Legacy 68668]

Can you change

temp = "NEXT ENTRY" & _
      CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll

to

temp = Space(FileLen(fn))
Open fn For Binary As #1
    Get #1, , temp
Close #1
temp = "NEXT ENTRY" & vbCrLf & temp

 

[chrisscotty]

I highlighted the A column as well as not highlighting it.

No error message but it erased the first entry .

What is this particular macro supposed to do ?.


PS it could be something I am doing wrong.

Thanks for your continued time and effort !

 

[Legacy 68668]

Can you show me your code ?