Creating Database of Names from Poor Electronic System Output with Primary Job Role

[fedewaj]

Hi Everybody,
Previously, I posted on this topic (Creating Database of Names from Poor Electronic System Output) and I was able to create the list of names that I needed through a VBA written by JohnTopley - unfortunately I was not specific in stating that I needed Columns C-J included in converting K and L into this list of names.

I'm hopeful that somebody can help me now update that list of names with the role that those persons were entered under in the electronic system (Principal Investigator - labeled as PI Last and PI First in columns C and D, Research Coordinator as RC Last and RC First, Primary Administrative Contact as PAC Last and PAC First, etc.). The additional criteria for assigning this role is based on the greatest number of times that person is assigned that role; for example, if I am a PI 2 times but a Research Coordinator 10 times, I want my primary role to be pulled out as Research Coordinator.

If somebody can not only create this but also update JohnTopley's VBA (example database and his VBA attached to thread) to include all users from Columns C and D as one person, E and F as one person, G and H as one person, I and J as one person, and then break K and L into the individual people with their primary job role, that would be amazing.

[AB33]

If the output is shown on sheet output, just add this line

ws2.Cells(rr, 3) = First_Last(1) & " " & First_Last(0)

Please Login or Register  to view this content.

[fedewaj]

Hi AB33,
That creates a combined name in column C in Output but it doesn't include any persons from C&D as one person, E&F, G&H, or I&J in the output. This doesn't also include the request for the VBA to look for the most common job title for each outputed user.

[AB33]

Please attach the desired output.

[fedewaj]

Hi AB33,
You'll see in the updated example that I've included in the output users from Columns C&D as one person through Column H to include them as potential names in our ultimate database. JohnTopley's VBA pulled in users from K and L but I need all the users from each of these studies to be pulled into the database in the easiset fashion since I'll have to do this every month for 1,000-10,000 studies. This is the primary request and the most important, i need a list of names from the electronic system output that will take me seconds to run instead of requiring more of my time.

The seondary request is to have the VBA review the list of names and determine which column ithe name falls into the most and then assign that name that particular job role. This could be different from my HR database of job titles and could be useful data for reporting purposes.

[AB33]

Ahm,
It is difficult to understand how are you getting column D.
First, John's code does not touch column K, so I do not know where column K is appearing on the code.
Second, you are pulling in columns C and D below the last split of column L, i.e. rows 37 and below. I presume you do not want to mix these data with column L, that is, always appear separately.
Where and how are getting these Primary Job?
I see PI and RC. How do you come up with the maximum and minimum values?

[fedewaj]

Hi AB33,
To break it down to the simplest terms I can make it: I need all the names of every user that my electronic system outputs in the report I'm given every month in a simple to use list of names. My original issue that John Topley answered for me was splitting columns K and L apart because that output needs to converted into a single column of all those names. I need the PI (Columns C and D), the RC (Columns E and F), the Faculty Sponsor (Columns G and H), the PAC (Columns I and J), the Co-Investigators (Column K), and Other Study Personnel (Column L) all converted into a single column of names through the easiest process possible. Mingling them is not a problem because I just need a database of names that I can correlate to my HR's database of personnel to utilize Vlookup.

Let's just ignore Column D in Output for now because the list of name is what I absolutely need.

Please let me know if this is still confusing.

[AB33]

Yes, I am still not sure I understand your request.
I have amended the code just to add column C and D, but there are not added in any order.

[fedewaj]

Hi AB33,
I went ahead and added in columns G-J using your code additions. That is a good first step. Now, I know the code is working on Column L to break those names up but I also need to break up Column K because those names are combined by the electronic system's output like Column L is. I tried to add that code but it screwed the macro up until I removed it. Can we add that in and then I'll at least have an accurate list of names from this report?

[AB33]

That is easy to add, but I am not sure which columns should be added from column K split. Does it follow the same split as column L?
To be frank with you, I am still not sure if this what you wanted.

[fedewaj]

It does follow the same split as Column L so this works perfectly. I'll take what you sent, addd my data to it, and then I can use that monthly to create my list of names.

Thank you so much!