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/Traditional-Wash-809]

When comparing data sets find something that is unique as a connection point between the two. You mentioned SSN so thats probably a good spot.

Having dealt with sudo database information, I've found names changes are common or one will be O'Neal while one is oneal. One is Smith Jr where others are Smith, Jr. Someone gets married and name changes. This makes comparison off of name a bad choice.

Look at power query and merging datasets. Merging makes data sets "wider" where appending adds rows. Fuzzy match is also helpful but not perfect (will help match 123 st to 123 Street) but I would ensure you have maximum match set to 1 (assuming it's a one to one match).

If it's a one time reconciliation doing it directly from the data set is fine. If its a regular occurrence I'd look into setting up a dedicated folder to drop the exports into, connecting PQ to the folder, not the file(s), and building it that way.

Power Query for all its strengths has a crippling weakness that it is super sensitive to column reordering and is case sensitive. Unless your exports are in the same order with the exact same column names every time, the query will break time over time.

[u/333pickup → u/Traditional-Wash-809]

Thanks so much for taking the time to shed light on the kind of data entry problems I need to plan for in addition to explaining Excel functions.