Creating a spreadsheet from a text list pasted into Excel

I have a list if data that comes off a text based property list. I haven't done any macro development but I need  a macro that make a new worksheet in the Excel File, copies data from one worksheet and pastes it into the appropriate column in a new worksheet.

Attached is a sample of the text list pasted into excel.  The list pasted into Excel has 13,740 rows.

Below is a layout I'm trying to get this data formatted into in a 2nd worksheet.

Sale date      Case#  Opening Bid   Lender                        Address                     Attorney
6/1/2013      12345      50,000      Avenue Mortgage      1 Main St, My Town  John Lawyer

How do I write this macro?
LVL 25
Tony GiangrecoAsked:
Who is Participating?
byundtConnect With a Mentor Commented:
Assuming that the data is like the sample .xlsx file, here is a macro to produce the requested table on Sheet2:
Sub Normalizer()
Dim cel As Range, rg As Range, targ As Range
Dim i As Long, j As Long, k As Long, n As Long
Dim s As String, ss As String
Dim v As Variant, vData() As Variant
With ActiveSheet
    Set rg = .Range("A1")   'First cell with data
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))    'All the data in that column
End With
n = Application.CountIf(rg, "Sale Date:*")
ReDim vData(1 To n, 1 To 6)
For Each cel In rg.Cells
    s = UCase(cel.Value)
    If s <> "" Then
        j = InStr(1, s, ":")
        ss = Application.Trim(Mid(s, j + 1))
        If s Like "SALE DATE:*" Then
            i = i + 1
            If ss <> "" Then vData(i, 1) = CDate(ss)
        ElseIf s Like "CASE*:*" Then
            vData(i, 2) = CStr(ss)
        ElseIf s Like "OPENING BID:*" Then
            If ss <> "" Then vData(i, 3) = CDbl(ss)
            If Application.CountIf(cel.Resize(3, 1), "*:*") = 1 Then vData(i, 4) = cel.Offset(2, 0).Value
        ElseIf s Like "PROPERTY ADDRESS:*" Then
            If InStr(1, cel.Offset(1, 0).Value, ":") = 0 Then vData(i, 5) = cel.Offset(1, 0).Value
        ElseIf s Like "ATTORNEY:*" Then
            If InStr(1, cel.Offset(1, 0).Value, ":") = 0 Then vData(i, 6) = cel.Offset(1, 0).Value    'Ignore the number on the line after attorney's name
        End If
    End If
With Worksheets("Sheet2")
    Set targ = .Cells(.Rows.Count, 1).End(xlUp)
End With
If targ.Row = 1 Then
    targ.Resize(1, 6).Value = Array("Sale date", "Case#", "Opening Bid", "Lender", "Address", "Attorney")
    targ.Resize(1, 6).Font.Bold = True
End If
Set targ = targ.Offset(1, 0).Resize(n, 6)
targ.Columns(2).NumberFormat = "@"
targ.Value = vData
End Sub

Open in new window

Danny ChildConnect With a Mentor IT ManagerCommented:
that data set is pretty horrible - blank rows, no clear demarcation between the data, variable lengths of fields and so on.

Is there any chance of a slightly friendlier file format?  ie CSV, TSV

you can extract some of the data by using formulas - this works for the cells that have the data and the heading in the same cell
Anthony MellorChartered AccountantCommented:
it might be interesting to see the actual text file rather than what has been loaded into excel... grasping at straws perhaps, with a good few rows shown in it. I am thinking carriage returns for example. and the colon looks reliable..
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tony GiangrecoAuthor Commented:
Yes, I agree it's ugly. I can normalize it into a CSV file, but then how do I create csv data that represents rows and change it to represent columns so I can import it into a new spreadsheet?
Anthony MellorConnect With a Mentor Chartered AccountantCommented:
I rather meant let us see the source file before it finds its way into excel; looking for things like whether the spacings are fixed.. what if we delete all the legends (headings) are we left with data that is 100% defined by where it is? Also whether there may be other hidden delimiters in the source file, such as <CR>, just exploring what the end of line EOL code might be.

to answer your question I can see the VBA mavens solving that with a FOR loop, if the locations are fixed.
Tony GiangrecoAuthor Commented:
The data contains personal information that cannot be posted here, but I've modified part of it. It's attached.
Patrick MatthewsCommented:
Please post a sanitized text file.  Better to work with something as close to the source as possible.
Tony GiangrecoAuthor Commented:
Hi  byundt, I appreciate your help. I'm not familiar with VBA. Where do I save it and how do I run it?
Anthony MellorChartered AccountantCommented:
not familiar with VBA: even more interesting for us to see a sanitised raw text file - the problem with giving us the results already in Excel is you are forcing the exclusion of  various methods Excel (at the least) has for processing the importation of text based data.
Tony GiangrecoAuthor Commented:
Yes, I had to sanitize it before posting. I'm sure you understand the importance of not uploading personal info.

Any suggestions on how to run that VB code?
Anthony MellorChartered AccountantCommented:
We are not understanding each other here.

You are uploading an xlsx file.

We are asking for the file from which you are taking the data, BEFORE it goes in to Excel - and sanitised of course. Sorry I myself cannot help with VBA, which is probably your best tool for a solution in the end with this one.
Tony GiangrecoAuthor Commented:
Hi byundt, I was able to get the code to run. It worked great. I'm going to review it further and see if I need any adjustments.
Tony GiangrecoAuthor Commented:
Thanks for the help. It worked perfectly.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.