Parse Excel cells based on a stacked value

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!
pelampeGIS AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
is the 1-A cells merged?
0
tomfarrarCommented:
Did you attach the file you referred to?
0
pelampeGIS AnalystAuthor Commented:
Professor: NO, they are not merged.

Tom: sorry, I thought I did attach it.  But it's attached now.
Parsing.xlsx
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny ChildIT ManagerCommented:
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
FloraCommented:
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
Glenn RayExcel VBA DeveloperCommented:
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
ProfessorJimJamCommented:
i second Glenn's comment.  not possible without VBA
0
Glenn RayExcel VBA DeveloperCommented:
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
pelampeGIS AnalystAuthor Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
pelampeGIS AnalystAuthor Commented:
Thanks to everyone who participated!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.