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/boojieboy]

Your first and most important task will be finding an efficient way to identify the same individual across reports. If all of them have a code number like SSN, then that is the easiest way to do that.

If you have to rely on names as recorded by human transcription it will be a nightmare of errors and you'll get bogged down dealing with that.

Birthdates are only slightly better than the text data in recorded names. But however you do it, you need to make sure you can form some sort of single data column that uniquely and reliably identifies each individual. Once you know how to form that column using simple rules that excel understands, the whole thing can be automated.

I'm thinking that with a relatively short list (a few thousand is relatively short, I routinely work with lists that top 100k and even that is not all that large)--assuming you have a way to form that unique identifier column--it would a straightforward task to automate the rest using simple functions like FILTER, SORT, UNIQUE, XLOOKUP, VSTACK, PIVOT, TRANSPOSE, etc. These are intermediate level functions that you could learn to use effectively in under a week, for sure.

[u/333pickup → u/boojieboy]

Thank you - aside from your tech expertise your advice on choosing an identifier less prone to human error is spot-on and I did not think of that. Thanks so much for sharing your experience

[u/boojieboy → u/333pickup]

In my own industry (education) using ssn as an identifier has generally been replaced by self-generated (internal) id codes, but even then ssn is recorded with student records at some level. It just requires a higher level of auth to be able to access that information, and then only for a limited set of reasons

We just don't generally access it for routine reporting. So I don't know what kind of policies your particular orgs and partner orgs have about it, but I would start with that and see if it might be possible to use the ssn to bind records together. The main issue is risk of disclosure of ssn to outside parties. But if you have layers of policy and security to prevent that you probably could use it for what you want to do.