Multiple Reports Master Sheet Question

[Hardey6ix]

So here’s the scenario…

I have to run multiple reports from multiple resources that all have different formats of output. The names of the employees or the main column all come out different. For example… report 1 = John Smith report 2 = John F. Smith report 3 = Smith, John etc… my goal is to have one master sheet for summary which is taking the data from the individual reports. I’m using VLOOKUP to find the data needed for each employee on the summary page. It’s a pain bc I’m having to add a new column on the summary page with the name format of each report and use that to reference the data set. Any suggestions?

 

[awoohaw]

Welcome to the forum. Do you have another identifying feature for the employee? An employee ID or Social Security Number (Do not share either of those here!). You can keep a reference table on another worksheet or create custom functions if the name formats need to be specific per report.

 

[Hardey6ix]

Welcome to the forum. Do you have another identifying feature for the employee? An employee ID or Social Security Number (Do not share either of those here!). You can keep a reference table on another worksheet or create custom functions if the name formats need to be specific per report.

Thank you for the welcome!
Unfortunately no, the reporting only has names and some sales codes, but that is not consistent on the multiple reports. Some might have it, some might not have it. I’m currently having to run / update roughly 10 reports to be able to get the data sets I need to have a master sheet. Of those 10 reports, the name format for the employees is 4 or 5 different styles. Even if I had to have a separate sheet for just employee names / formatting, I could make that work if it was required.

 

[awoohaw]

Not sure how extensive your company is. Is there a way to get the folks who create your data to standardize how they provide identifying information?

If that isn't possible, then you need to create a some kind of conversion data table where the source and name are tied to an identifying number. You'll have to have routines to add new records for new employees though.

 

[Hardey6ix]

Let’s just say the company is extensive haha, so creating a standard when it comes to reporting isn’t possible. But talking through this with you, gives me an idea with a conversion table… tell me if this makes sense and if it would be possible…?

Summary Sheet w/ all finalized data
Employee Sheet w/ all reporting formats attached to one employee
Individual Sheets w/ each report that needs to be run

And from the summary sheet, I can write my formula in the corresponding cells, to locate the proper format of the employee name from the Employee Sheet, and to go to the individual report to locate the data set I need… through VLOOKUP? Or an easier function / formula?

 

[awoohaw]

My suggestion would be to use power query and power pivot to create your conversion "fact table". I am by no means an expert on that, particularly power query/power BI. Essentially you need to pick which of your data sources is most robust as far as full names. Use the name column from that table as a starting point. Name the name column a unique name for that data souce like "Employee Name". Then create an identifying ID. Then do the same for the name columns in the other tables. Naming the name columns "Sales Report Name" and "Bonus Report Name" etc. The indentifying ID would have the exact same name in all tables. You'd hve to populate each tables ID column manually (or with a formula if you can - lookup against that first table) to put in the ID. Then merge all the tables on that ID, column. Of course if same employee appears twice in reports then that adds more complexity.

Since you say you have a large company, i really don't see how it would be an inconvenience to ask them to update the columns in their output for your reporting and analysis. Go for the know (a question never asked never gets a yes).

 

[Hardey6ix]

My suggestion would be to use power query and power pivot to create your conversion "fact table". I am by no means an expert on that, particularly power query/power BI. Essentially you need to pick which of your data sources is most robust as far as full names. Use the name column from that table as a starting point. Name the name column a unique name for that data souce like "Employee Name". Then create an identifying ID. Then do the same for the name columns in the other tables. Naming the name columns "Sales Report Name" and "Bonus Report Name" etc. The indentifying ID would have the exact same name in all tables. You'd hve to populate each tables ID column manually (or with a formula if you can - lookup against that first table) to put in the ID. Then merge all the tables on that ID, column. Of course if same employee appears twice in reports then that adds more complexity.

Since you say you have a large company, i really don't see how it would be an inconvenience to ask them to update the columns in their output for your reporting and analysis. Go for the know (a question never asked never gets a yes).

I’ll have to deep dive into your idea more when I have time. Thanks for the response.

As far as my company, it’s a corporate issue as far as generating new formatting for reports. Unfortunately there is an infinite number of reports that can be generated, and they come from 100s if not 1000s of programs, servers, data sets etc. so it would be impossible to have streamline format across the board.

 

[awoohaw]

I've been in that world. Find a data definition of all the reports that are generated from the various data owners/sources and see what can be done. Data is not in infancy anymore, and there are nearly always a way to get meaningful data. It may take some leg and grunt work, but it is very possible.

Well, you only need to ask about the reports that are important to you.

Another thought. Although you may get data from reports A, B, C. There could be reports L,M, or N that have the data you need for all reporting with a unifying ID number.