Using Parsing and Multi-Field to format document

davidlocke

I have a file that comes out of my tax software that need to break out into columns and apply some kind of multi-field condition to.

The basic flow is data is stacked in the following order:

1. IRS Form Number 1120 Pg. 1 Line number - Line Description
2. Software's TRC No - TRC Description
3. Software's TCC No - TCC Descripion
4. Our Company's GL A/C No - G/L Description

Some inherent data flow considerations are:

• Form Line Numbers are alphanumeric and will include letters and numbers and will not be the same number of characters
• Form Line Descriptions while generally alpha numeric, can contain special characters (":", "&", "%", and "/" being the most common)
• TRC No are /d{2}-/d{3}
• TRC Descriptions while generally alpha numeric, can contain special characters (":", "&", "%", and "/" being the most common)
• TCC No are /d{3}
• TCC Descriptions while generally alpha numeric, can contain special characters (":", "&", "%", and "/" being the most common)
• G/L AC No.s are /w{6}
• G/L Descriptions are Descriptions while generally alpha numeric, can contain special characters (":", "&", "%", and "/" being the most common)
• Not every Line Number has a TRC Number
  • If it does not have a TRC Number, it will not have a TCC Number, or G/L Number
• Not every TRC Number has a TCC Number
  • If it does not have a TCC Number, it will not have a G/L Number
• Not every TCC Number has a G/L Number
• If a Line Number has a TRC Number, it can have 1 or more TRC Numbers
• If a TRC Number has a TCC Number, it can have 1 or more TCC Numbers
• If a TCC Number has a G/L No., it can have 1 or more G/L Numbers
• Each G/L No can have ONLY ONE Line No, TRC No., and TCC No.

End Result would be to organize the G/L accounts by Line No and Description; TRC No. and Description; TCC No. and Description. Then I would concatenate the TRC No and TCC No. in a separate column.

GL Acct No.	GL Desc	1120 Pg. 1 Ln No.	1120 Pg. 1 Ln Desc	TRC/TCC Combo	TRC No.	TRC Desc	TCC No.	TCC Desc
400000	Sales	1a	Gross Receipts or Sales	30-100 010	30-100	Gross Receipts or Sales	010	Gross Receipts/Sales
400001	Product Sales-Manual	1a	Gross Receipts or Sales	30-100 010	30-101	Gross Receipts or Sales	010	Gross Receipts/Sales

Ideally, I would be able to have a filter near the end where True would be !Null G/L and False I still would be able to see the Line No/TRC/TCCs that do not have an account, but that still has the TCC on the same line with its TRC and Line No on the same row on the F.

I have tried working with Parse and for the life of me I can't get the parse to work.  I can text to columns, but because each is slightly different it creates more columns than I want and separates the TRC Number as it contains I dash. 

1120Pg1TB.xlsx

TonyA

Take a look at this. You'll need to check my logic carefully, I did this kind of quickly. Especially the last three tools where I decided when to keep rows that didn't have GL values.

I focused on getting everything into rows and getting rid of unneeded rows. So there is a little cleanup left to do including creating the concatenated column.

Also, since I did do this quickly you might be able to tighten it up.

Edit:

One more thing. I did not do any error checking. For example, I'm only checking to see if a change in line number is followed by a change in TRC and keeping the line if it isn't. I'm not checking to see if a change in line number is followed by, for example a new GL, which would be an error.

I just looked through your example data and that does happen in one place at the end of the data set, so you might want to account for that. It would just require some more complex logic in the last Multi-Row Formula tool.

Edit (2): I found an error that I just fixed. If you downloaded before 8am Pacific on May 1, please download again.

Parsing 1120 Pg1 TB (TA) v2.yxmd