• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi-Step Project Data Import with Name Translation

K

Kirk Keller

New Member
Good day everyone! I hope this finds you in good health.

I have a conundrum. I have an excel spreadsheet that I use as a dashboard to represent project data at a very high level (Current Project Data). Recently, I've been asked to display additional milestone information that is coming from a separate Project Management system (Import Project Data). Unfortunately, there is a significant discrepancy between the project names in (Current Project Data) and (Import Project Data). So much so that I've not been able to use vlookup or index to manipulate the data as I need to. That constitutes the name translation part of the conundrum.

The next part is that once the name translation is solved and the data is imported, I need to convert the data from it's current (Import Project Data) format into (Post Import Data) Format.

At a high level, I need the import file to reference and adopt the project name from the current project list, and once that is complete, migrate the data into a post import format that I can then use to create the milestone information. I've uploaded a basic file with the tab names matching the references used above.

Help!!
 

Attachments

For the name translation, I think you will have to give us some very specific examples of what you are having to deal with. Perhaps one of us can see a pattern that can be used? Otherwise, I'm not sure what help we can be.

For the data arranging, do you want us to make the Import Project Data look like the Post Import Data, that is certainly doable. Worst-case we right a code that steps through the data and transfers it to the Post Import Data sheet. I will say, IMO the layout of Post Import Data is very cumbersome. Do you really need it like this, or is it just an intermediary step?
 
Thank you for your response! I will see about providing additional information. The Post Import data table can be laid out differently, provided we have a one to many layout between the project name and the tasks associated with that project. The goal for the Post Import data is to use the data to create a gantt chart in another page of the master worksheet that will dynamically populate when a specific project code is selected.
 
Hi Kirk ,

Your Current Project Data tab lists two identities of a project viz. the project code and the project name.

Your Import Project Data tab lists only the project name.

Your requirement is that we match the 'project' in one tab with the same 'project' in the other tab ; the question is how ?

For example , one project code is ABC_123 ; one project name is Project Name 4.

On the Import Project Data tab , one project name is ABC Project Name 4_123 ; do we match this with ABC_123 , whose corresponding project name is Project Name 1 , or do we match this with Project Name 4 , whose corresponding project code is ABC_126 ?

Narayan
 
NARAYANK991 said:
Hi Kirk ,

Your Current Project Data tab lists two identities of a project viz. the project code and the project name.

Your Import Project Data tab lists only the project name.

Your requirement is that we match the 'project' in one tab with the same 'project' in the other tab ; the question is how ?

For example , one project code is ABC_123 ; one project name is Project Name 4.

On the Import Project Data tab , one project name is ABC Project Name 4_123 ; do we match this with ABC_123 , whose corresponding project name is Project Name 1 , or do we match this with Project Name 4 , whose corresponding project code is ABC_126 ?

Narayan
Click to expand...
Hi Narayan, thanks for your reply!

I need to match and then concatenate the Project Name 1 piece of the project name in the Import Project Data tab to the Project Name 1 in the Current Project Data tab.

For instance;

Import Project Data Current Project Data
Project Name 1 needs to become Project Name 1
Project Name 2 needs to become Project Name 2
ABC Project Name 3 needs to become Project Name 3
ABC Project Name 4_123 needs to become Project Name 4
B_Project Long Name 5_ABC_123 needs to become Project Name 5

The problem I have is that the project name in Current Project Data needs to remain constant for reporting purposes. The project name coming out of the export file to be imported, is inconsistent and can change multiple times throughout the life of the project and the permutations are not consistent in form. There is however a constant component of the name that we should be able to match, but I've not been able to do it. Once that is doable, the remaining data (26 fields worth) is easy as the columns and data match. The project name itself is the only mismatch and any of the Import Project Data names can vary. Essentially, Project Name 1 could become CSP_ADY-123_Project Name 1 in the import file at the next import and will need to be matched to the Current Project Data, Project Name 1.
 
Narayan, I greatly appreciate your efforts! I ran the test on actual data and it returned only one translation out of the group. I cannot post the info to this site, but can I send it to you directly so you'll see what is happening?
 
You must log in or register to reply here.
Back
Top