Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I have a spreadsheet that uses a string convention to convey levels of detail about individual "cases."

The convention is as follows: There are 5 main categories for the data in the string. 1=CATEGORY, 2=TOOLS, 3=DOCUMENTATION, 4=PROCESS, 5=JOB AID. Not every case may have all of these, some may have as few as one main category.

Each category can have multiple sub categories broken down by 1a, 1b, 1c or 2a, 2b, 2c, etc for each 5 category. I will have a table of codes in column 1 with their corresponding text value explanation to the right in column B

Date is separated by "~", coach is separated by "^" and agent is separated by "^". Main category is separated from sub category by ":" and sub category is separated from sub category by "," whereas sub category is separated from a new Main category by a ";". Each row of string can be ended from the next by "*".

two example strings separated by row:

2/1/2013~Coach A^ Agent B^1:1i;2:2a;3:3a,3d,3l;4:4a*

2/1/2013~Coach B^ Agent C^1:1i;2:2b;4:4b*

Here is a sample breakdown of category in values:

Guide on Sample Codes

1 CATEGORY 1i Incorrect:VG:QOC 1j Correct:VG 2 TOOLS 2a Macro:Used 2b Macro:Unused 3 DOCUMENTATION 3a TAT:Missing 3d ROUTING:Missing 3e ROUTING:Unecessary 3i STORY:Missing Impact to Health 4 PROCESS 4a CNX Checklist Not Used 4b CNX Checklist Used

I want output to come in table form something like this for the above sample string:

DATE COACH AGENT Level1 Level2 Level3 Level4 Level5 2/1/2013 Coach A Agent B CATEGORY Incorrect VG>QOC
2/1/2013 Coach A Agent B TOOLS Macro Used
2/1/2013 Coach A Agent B DOCUMENTATION TAT Missing
2/1/2013 Coach A Agent B DOCUMENTATION ROUTING Missing
2/1/2013 Coach A Agent B DOCUMENTATION STORY Missing Impact to Health
2/1/2013 Coach A Agent B PROCESS CNX Checklist Not Used Missing Impact to Health
2/1/2013 Coach B Agent C CATEGORY Incorrect VG>QOC
2/1/2013 Coach B Agent C TOOLS Macro Unused
2/1/2013 Coach B Agent C PROCESS CNX Checklist Used

Here is a link to a similar thread: Reference Excel String Parsing

I've attached a sample file with an attempt, examples of codes, strings, and desired output. enter image description here

share|improve this question
What have you tried (provide your code) and where exactly are you stuck? – Glenn Stevens Feb 12 at 13:48
Is that possible to have SEVERAL records for Coach A^ Agent B^ e.g. for different dates? or for any date given there's only 1 record for the given coach-agent pair? – Peter L. Feb 12 at 14:10
Which part of code are you having trouble with: parsing each record into individual elements?; looking up description for each code? navigating through the records?; writing the parsed elements back to the sheet? -- or are you looking for worksheet formula solution? – chuff Feb 12 at 14:42
Code attempted provided in previous question linked above: Excel String Parsing – user1005237 Feb 12 at 15:46
For each date, there will only be one coach and one agent. To that end, each row of string will only have one coach and one agent assigned. Ideally worsheet solution. <a href="docs.google.com/file/d/0By-qzKpmKQkgVTBxRmlhUnhwbDQ/…; – user1005237 Feb 12 at 15:52

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.