Creating a spreadsheet from a text list pasted into Excel

Posted on 2013-05-16
Last Modified: 2013-05-22
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?
Question by:Tony Giangreco
LVL 23

Assisted Solution

DanCh99 earned 50 total points
ID: 39173114
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

Expert Comment

ID: 39173190
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..
LVL 25

Author Comment

by:Tony Giangreco
ID: 39173221
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?

Assisted Solution

anthonymellorfca earned 50 total points
ID: 39173227
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.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39173277
The data contains personal information that cannot be posted here, but I've modified part of it. It's attached.
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39173511
Please post a sanitized text file.  Better to work with something as close to the source as possible.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 80

Accepted Solution

byundt earned 400 total points
ID: 39173743
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

LVL 25

Author Comment

by:Tony Giangreco
ID: 39174448
Hi  byundt, I appreciate your help. I'm not familiar with VBA. Where do I save it and how do I run it?

Expert Comment

ID: 39174500
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.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39174520
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?

Expert Comment

ID: 39174611
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.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39174637
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.
LVL 25

Author Closing Comment

by:Tony Giangreco
ID: 39187330
Thanks for the help. It worked perfectly.

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now