Solved

Parse Excel cells based on a stacked value

Posted on 2014-11-14
13
105 Views
Last Modified: 2014-11-14
Hello,

I have received a spreadsheet (SS) from a client that has cells with stacked values in it.

Below is an example of the column name and 3 stacked values:

Parcel_ID
45462A
45462B
54621A

However, the client has only associated only ONE Tract_ID value associated with the 3 stacked values.

See below:

Tract_ID          Parcel_ID
                         45462A
1-A                   45462B
                         54621A

In the case above we have a 1:Many relationship (ONE Tract_ID to MANY Parcel_IDs).

My goal is to parse this Parcel_ID field so that each row contains ONE Parcel_ID and ONE Tract_ID, or a 1:1 relationship.

See below:

Tract_ID          Parcel_ID
1-A                   45462A
1-A                   45462B
1-A                   54621A

For a better idea of what I'm talking about please open the attached SS "Parsing.xlsx".  It is a model of the actual SS.   The SS has many more rows (~70) and columns (~15).

In it there are two tabs;  BEFORE Parsing and AFTER Parsing.

The AFTER Parsing tab is what I'm trying to achieve.

Due to the urgency of this matter, I'm going to push the points total to 500.

TIA for your replies!
0
Comment
Question by:pelampe
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40442806
is the 1-A cells merged?
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40442812
Did you attach the file you referred to?
0
 

Author Comment

by:pelampe
ID: 40442821
Professor: NO, they are not merged.

Tom: sorry, I thought I did attach it.  But it's attached now.
Parsing.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40442865
Hi, this macro will parse out the lines and create unique rows for each Parcel_ID.  It assumes the first sheet in the workbook is the source data but doesn't care what the name of the sheet is.  It puts all the results on a separate sheet called "Results."
Option Explicit
Sub Tract_Parcel()
    Dim rng As Range
    Dim cl As Object
    Dim arrPID() As String
    Dim x, intRow As Integer
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Sheets("Results").Delete
    Sheets.Add After:=Sheets(1)
    ActiveSheet.Name = "Results"
    Sheets(1).Select
    Sheets("Results").Range("A1:E1").Value = Range("A1:E1").Value
    intRow = 2
    
    Set rng = Range("A2", Range("a2").End(xlDown))
    For Each cl In rng
        arrPID = Split(cl.Offset(0, 1), vbLf)
        For x = 0 To UBound(arrPID)
            With Sheets("Results")
                .Cells(intRow, 1).Value = cl.Value
                .Cells(intRow, 2).Value = arrPID(x)
                .Cells(intRow, 3).Value = cl.Offset(0, 2).Value
                .Cells(intRow, 4).Value = cl.Offset(0, 3).Value
                .Cells(intRow, 5).Value = cl.Offset(0, 4).Value
            End With
            intRow = intRow + 1
        Next x
    Next cl
    Sheets("Results").Select
    Range("A1:E1").EntireColumn.AutoFit
    
End Sub

Open in new window


Example workbook attached.

Regards,
-Glenn
EE-Parsing.xlsm
0
 
LVL 23

Expert Comment

by:DanCh99
ID: 40443075
Would regular LEFT, MID and RIGHT formulae help here?
=LEFT(B2,6)
=MID(B2,8,6)
=RIGHT(B2,6)

Surely, the fact that each cell is multi-line is fairly irrelevant?
0
 
LVL 5

Expert Comment

by:Flora
ID: 40443128
Glenn

thank you.  though i am not the author of this question, but i would love to see how this is doable by using excel formula Not vba?

should i create a new thread?  as i have similar question but want to use formula not VBA
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40443362
I don't think this particular problem can be solved without VBA.  There is no indication that the number of values separated by line feed characters will always be three.  However, if that was true, a separate worksheet could be set up to produce unique records.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40443365
i second Glenn's comment.  not possible without VBA
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40443435
If there were ALWAYS three values separated by line feed characters, here are the three formulas that would extract them (assuming source sheet is "Sheet1" and data starts on A2):

1st value: =LEFT(Sheet1!B2,FIND(CHAR(10),Sheet1!B2)-1)
2nd value: =MID(Sheet1!B2,FIND(CHAR(10),Sheet1!B2)+1,FIND(CHAR(10),Sheet1!B2,FIND(CHAR(10),Sheet1!B2)+1)-FIND(CHAR(10),Sheet1!B2))
3rd value: =MID(Sheet1!B2,FIND(CHAR(10),Sheet1!B2,FIND(CHAR(10),Sheet1!B2)+1),99)

(99 is arbitrary; just needs to be greater than the longest number of characters for any given Parcel_ID)

-Glenn
0
 

Author Comment

by:pelampe
ID: 40443666
Glenn - the VBA worked perfectly.  I did notice that there were only 5 cols built into the macro, which is OK.  But in the event that needed 2 more columns, is it as simple as adding these 2 additional rows (lines 10 & 11 below)?

Set rng = Range("A2", Range("a2").End(xlDown))
    For Each cl In rng
        arrPID = Split(cl.Offset(0, 1), vbLf)
        For x = 0 To UBound(arrPID)
            With Sheets("Results")
                .Cells(intRow, 1).Value = cl.Value
                .Cells(intRow, 2).Value = arrPID(x)
                .Cells(intRow, 3).Value = cl.Offset(0, 2).Value
                .Cells(intRow, 4).Value = cl.Offset(0, 3).Value
                .Cells(intRow, 5).Value = cl.Offset(0, 4).Value
                .Cells(intRow, 6).Value = cl.Offset(0, 5).Value
            End With
            intRow = intRow + 1
        Next x

Open in new window

0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 40443681
pelampe,  yes, that should work just fine.    You'd also want to modify this line (originally line 15 of example code):
Sheets("Results").Range("A1:G1").Value = Range("A1:G1").Value

Open in new window

0
 

Author Closing Comment

by:pelampe
ID: 40443796
Thanks to everyone who participated!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

10 Experts available now in Live!

Get 1:1 Help Now