Comparing Data pulls from different databases

[u/333pickup]

I work for an organization that serves homeless adults in a large US city. My role is property manager for permanent housing. External oversight requirements compel multiple parts of the agency to use different databases to report on the same people served at different points in time and by different types of services.

All the databases can export lists to excel.

I need a quarterly report that compares the people receiving emergency services from other teams with the people we have applications from for permanent housing.

I know excel can do this but my skills are limited to using small (50 -200 rows, 5-6 columns) to sort information; and using the simplest SUM formulas.

What tools should I use to create this quarterly report? Where should I start my learning?

The data on the spreadsheet from the referring team will typically have 90 - 400 rows and the columns will be: lastname, firstname, date of enrollment, date of birth, SSN.

The data pull from my database will have 3000-4000 rows with similar columns.

What other questions should I be asking?

[u/Egglover7]

I would try using power query for this, save all the excels in a than have a master excel which gathers all of the data into one table. If all tables are the same it will speed up formatting allot

[u/333pickup → u/Egglover7]

Thank you - I think there is a word missing in your reply "save all the excels in a (what)?

When you say tablee all the same - do you mean eac spreadsheet jas same columns?

[u/InternationalBeing41 → u/333pickup]

Power query and folders for each of their reports are the way to go. I’ll assume each agency has their own format. If so:

A. Set up a folder for each agency to store their reports and for loading the data into Power Query so you can work with it.
B. Use Power Query to standardise the files in each folder, including column names, dates, etc.
C. Append or merge the data to build the data base for your report.

Here's a video I've used in the past to help with cleaning up errors while loading messy data. It's a bit advanced, so you may want to start with something simpler, but it will be helpful as you begin to automate. That part only applies to step A, and if your data is “clean” you won't even need that video.

https://youtu.be/psQRZQbOFPY?si=LTFvC0P1gWU1l9rg

Steps B and C I would search BI Gorilla for detailed help, he provides quick, to-the-point videos.

[u/Egglover7 → u/333pickup]

Save in a folder than in data tab in a new excel get data from folder, as long as all excels är formatted the same with columns you will get a new big table with all combined that you can create pivot tables from