HI newbie here. Hello, team members. I am working on a file that should be easy to clean up but I am trying to find the most efficient way to do so. The file has multiple columns but this are the critical ones (in this format):
| Employee id | last name | first name | Course A title- status | Course A title- completion date | Course B title - stat | Course B title- completion date | Course C title- st | Course C- completion date |
| 1234 | blue | ivy | completed | 12/8/19 | In progress | | | |
| 1234 | blue | ivy | | | completed | 12/8/19 | | |
| 1234 | blue | ivy | | | | | In progress | |
I want it cleaned like this:
| Employee id | last name | first name | Course title | status | completion date |
The biggest issue are these:
a) there are about 10-15 columns with the completion status and an additional 10-15 columns with the title of the course and the completion date. The course title columns are not uniform. For example, sometimes the status column is named "Course title" stat, sometimes "Course title" status", sometimes the column has the course title but no "status" in the column's name. Same with the "completion" heading. I tried using Regex to first cleaning up the titles so they would be uniform and then join every thing using the column name. But that did not work because I could not figure out how to use regex to remove all unwanted characters and have all columns the same name.
b) The other issue is, there are several records for the one person. So, sometimes we end up with having about 30 records for the person to indicate their status for the 10 courses, because each record indicates the completion of the course.
I hope this is clear. I tried using transpose and formula using regex but I am a bit stuck now.
Thanks
