Jul 04 2020 03:18 PM
I have a column in a spreadsheet with 2500 plus rows. Within the column, is basic job description information. So you have elements Job Summary, Job Description, Primary Responsibilities with various billeted lists separated by hard returns, Education Information, Preferences, and Skills and Competencies.
I need to parse the column so that the paragraphs stay together into new columns. So for example, I need to move Job Summary into a new column, Job Description into a new column, Primary Responsibilities into a new column (This includes each billeted list for every job), Education Information into a new column, etc.
I can separate into a new column based on the Hard Returns within the file, but it separates the primary responsibilities into a separate column for each bullet on the job. This means that I have to concatenate it back together and this becomes difficult because some jobs have 4 bullets while others may have 15 bullets. I am trying to find a way to separate to keep the major sections together based on m
Jul 04 2020 03:26 PM
Could you please submit sample file with dozen or so rows (not 2500+) to illustrate what do you mean.
Jul 04 2020 03:43 PM - edited Jul 04 2020 03:51 PM
@Sergei Baklan Here is two rows of data from the spreadsheet. From the spreadsheet, you can see it is a basic job description all in one cell with Excel. I want to separate the one cell into multiple columns based on the major sections within the job description.
So I want the opening paragraph in one column, the Primary Responsibilities in the next column, Requirements in the 3rd column, Preferences in the fourth column and then Skills and Competencies in the 5th column.
The data greatly varies for each job description. So you could have 3 bullets in one primary responsibility and in in another job you could have 15.
Let me know if that helps.
Jul 04 2020 04:46 PM
@lostvol , I used Power Query to get the information. Please add a few more Job Descriptions to the table in the "JobDescriptions" tab. Just paste in the rows starting A4 and the Table will automatically expand. Then click on Data->Refresh All to update the "Summary" tab. Let me know if this is what you are looking for.
Jul 04 2020 05:21 PM
@TheAntony This absolutely would work, but I am receiving an error message. I just went ahead and posted all the descriptions in the file for you to see the error that I am receiving.
This so freaking awesome if you can help me get this working....I have been trying for days to make this work and you did it in a matter of minutes.
Let me know if you can make this work.
Jul 04 2020 05:29 PM
Jul 04 2020 05:46 PM
Jul 04 2020 05:49 PM
@lostvol1420 , the job descriptions weren't "clean". There were 14 empty ones. Also there are a few 100 items that don't conform to the structure the others are in. I fixed a few of the issues, but review thoroughly for the exceptions.
Jul 04 2020 05:53 PM
Jul 04 2020 05:53 PM
@lostvol1420 , correct. Now this will run without errors, but will not have the correct content in the correct column if a Job Description isn't coded correctly. By that, I mean it should have the Section Headers of Requirements, Primary Responsibilities, Preferences, etc. Once you fix those in the data, do a refresh and it will pull through all the items.
Jul 04 2020 06:02 PM
Jul 05 2020 09:13 AM
I'm not sure what is the goal, but I'd suggest to separate not only by columns but as rows as well. Please check in attached file.