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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Assisted Solution

by:Danny Child
Danny Child 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

by:Anthony Mellor
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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Assisted Solution

by:Anthony Mellor
Anthony Mellor 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.
LVL 81

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

by:Anthony Mellor
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

by:Anthony Mellor
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

617 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