Solved

Parse Excel cells based on a stacked value

Posted on 2014-11-14
13
110 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
[X]
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
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 26

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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
 
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 26

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

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 will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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