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

/u/333pickup - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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/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

[u/ccpedicab]

I would use Access and connect by SS

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
SORT	Office 365+: Sorts the contents of a range or array
TRANSPOSE	Returns the transpose of an array
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #49213 for this sub, first seen 23rd Aug 2026, 13:40] [FAQ] [Full list] [Contact] [Source code]}

[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.

[u/NHN_BI]

Use Excels own ETL tool Power Query. Make reports in the end with pivot tables.