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]

For that matter city and province and zip..though it looks like I can do most of those with by separating by comma.

Thing is what happens when there is no address or city etc?

 

[Legacy 68668]

Can you show me one of the "Address" in result and the result that you want ?

 

[chrisscotty]

Mr Nal Mjjjjj 603-43 Txxxx Park Drive k, OH 124564, USA NEXT ENTRY


I want the name to be in a separate header and then the address to be in a separate header and the city state/province country in a different header.


The city and state are separated by commas but the problem arises when there is no address or city and I want the rows to be consistent so I can export as csv and import in to contact manager.

Thanks..yet..again.

 

[Legacy 68668]

Does this make any difference ?

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)
n = 1
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In Split(temp, vbCrLf)
        If Trim(Replace(e,Chr(160),"")) <> "" 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 = "^[\t\r\v\s]*$"
    .Global = True
    .MultiLine = True
    CleanTxt = .replace(txt, "")
End With
End Function

 

[chrisscotty]

The same.

 

[Legacy 68668]

Hummm

Name field in result with blanks should incl. string.

Can you try to find out what is that string by entering a formula in any cell

=Code(A2)

where A2 seems Blank...

 

[chrisscotty]

Sorry...not sure what you mean ?

 

[Legacy 68668]

Some rows in name column in the result seems blank and it appears in the address column, right?

What I need to know is what's in the blank name row and you can get it with the formula in any available cell = Code(A1) where A1 is a blank cell in the name column.

 

[chrisscotty]

Sorry for the delay ..I was out of town and extremely busy the last few days.

The name is always there now.

The problem is I need to separate the info that is in column

as they all have the name but some do not have the address or city or state etc.

Your code is working perfectly the problem is that some of the names just have the name because that is all that was there to begin with.

so
Mr. Brian Smith NEXT ENTRY

Ms. JoAnn Smith 35 ABC Drive Suite 100 Oakland, CA 123456, USA NEXT ENTRY

I want address in one column , the state in one column country etc etc.

But if the data is missing I still want it all to correspond.

I can just find and replace all NEXT ENTRY.

I can even run in a separate worksheet as the rest of the data is laid out perfectly.

 

[chrisscotty]

For= Code(A1) I get 78 when pasted in A2 for= Code(A2) I also get 77 when pasted in A2.

There are no empty cells in A1.