•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to pull unique records and specific record changes from two snapshots of the same database into a third sheet
Our take
In the quest to manage a large database of 7,500 records effectively, comparing snapshots over varying intervals can be challenging. This guide explores how to identify unique records and highlight specific record changes between two snapshots. By leveraging unique ID numbers, you can track records that have changed, entered, or left the database since the last snapshot. With the right approach, even without VBA, you can streamline this process, ensuring valuable insights are easily accessible for enhanced data management. Let's dive into the solution!
Hi y'all! I'm working with a large database of around 7500 records, with unique data spread across 100 columns per record. I'd like to take a snapshot of the database at regular intervals, and compare those snapshots across two intervals to get a list of:
- any records that have had any of their values change (sometimes a number, most often a unique text string)
- any new records that have entered the database since the last snapshot
- any records that have left the database since the last snapshot
I've found a solution that works if I take two snapshots on the same day, such that there wouldn't be any records leaving or entering the database (as records entering or leaving only updates once overnight). But I'd like to be able to compare across longer intervals as well, hoping to generate a weekly and monthly snapshot. Ideally, there would also be a way to highlight the specific column values that changed between the two snapshots, but I know that may be a separate problem entirely. Below is an example:
There are no dates associated within any given record, so I can't gauge which ones are newly added or leaving between snapshots by filtering the columns specifically.
My solution for comparing these snapshots on the same day works fine since the number of records is exactly the same. My formula for this is:
=IF(AND(EXACT(Post!2:2,Pre!2:2)),"",IF(NOT(ISBLANK(Post!2:2)),Post!2:2,""))
My understanding is that this formula will first check that all values in row 2 are the same across the "Pre" snapshot and the "Post" snapshot, and if they are exactly equal it will output a blank string. If they aren't exactly equal across all values, it will output the "Post" snapshot, while also making sure to output empty values as empty, rather than automatically filling them with 0s.
This formula won't work for longer intervals between snapshots as the number of records change. Essentially, I'm trying to figure out a way to compare each individual record (which each have their own unique ID number) to each other, outputting any records that have changed values during the interval and outputting any new records that have entered or left the database (and therefore don't have a comparison point in the other snapshot). Ideally, I could also set up some conditional formatting for those outputs, but its most important to just get the output pulling correctly first.
I'd love a solution that does not involve VBA (but I completely understand if that's the only way to do something like this)! Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Keeping a running total of data from one sheet, in anotherHi all. I'm after some help, or pointers as I couldn't think of the correct search terms to find out what I want to do. I have an Excel (M365) spreadsheet (sheet 1) which currently has about 1500 lines of data on it, with another 100 or so being added every month. On each line, there are 30 different bits of data. Most of this data will never change once it has been added, but there are about 6 fields on each line which MUST be updated every 4 months due to expiry. Sadly, due to the data source there is no way to do this automatically or via an API, so I need to check it manually. I also need to keep a running tally of some of the data that is in the sheet. For each line, the main fields that I'm interested in are the manufacturer (1 of 67), the model number (unlimited to a certain extent), serial number, two different locations (both 1 of 48), and a few others where the criteria is effectively yes or no There will also be a not known option for each. Due to the data, there is also some duplication which is required and we don't want the previous record to be overwritten. Sometimes there will be two records relating to the same item, sometimes more. For instance, we will have a record from the first occasion an item is seen, and then duplicates for every other occasion. Most of the data on the duplicate records will be the same but the initial date of entry will be different, along with a reference number. On another sheet (sheet 2) within the same workbook, I have a summary of the data, all of which is manually calculated. It includes the total number of records, the number of unique records, the number of records which are duplicates (i.e. the serial number appears twice or more), the number of items that have a particular result in one of the fields (ie. field yes/no/unknown) I also have the manufacturer details and the number of records linked to them. I want to extend this to the location details and number of records linked to them, so that I can say there are this number of records for this manufacturer, or this number of records in that location. Calculating all the stuff on the summary page is time consuming. What would be the correct formulas to use, so that when I put a new record on sheet 1 (or update one of the records), it automatically updates sheet 2, or what functions should I be looking for? I haven't got a drop down/data validation box for the manufacturers, but I could do this as I can't imagine we are likely to add any options to them. Likewise for the locations, as there will never be more than the 48 locations. This was confusing enough for me to type out, so hopefully it makes sense to someone else. I can possibly create a sterile copy of the spreadsheet if it helps submitted by /u/d4nfe [link] [comments]
- Inventory Audit using XLookup w/ multiple criteriaI've been working on a spreadsheet to keep track of some inventory. It has how many units entered and exited each day, for each item. Using this information, I have then compare it to the list of in/out inventory sent to us by a third party, basically to confirm that our numbers and their numbers match. This is where I'm struggling. I can't seem to find an efficient way of comparing these numbers that works. I've tried XLookup and Nested formulas, Index Match, etc and nothing seems to be working. I need the "total" numbers for each day from the first image to go into the "Double Check" column on the second image, matched for the item and date. I'm not sure if it's an issue because some is horizontal and some is vertical? Would love if anyone had a solution or better method to do this, thanks! Edit for more info: the image with the inventory, the "in" and "out" columns both pull data from a separate sheet that has all of the relevant information and then the "total" column is a formula that just sums the in/out with the previous days' inventory count. https://preview.redd.it/0r74aaf1nang1.png?width=1225&format=png&auto=webp&s=b024c5dad6c53315bfc91199aed1585e74512cba https://preview.redd.it/z799p9f1nang1.png?width=535&format=png&auto=webp&s=2df6c58a1f26c43d0d0b37801eb5d03cc05bdbfd submitted by /u/Fluffy-Reaction6723 [link] [comments]
- How to organize a sheet based on how many times a certain value in a column is duplicated, and have all other columns follow?Hope the title is descriptive enough... i feel like i always struggle to describe excel stuff efficiently. I am a complete excel beginner! So i have a dataset that is 3000+ rows long. For the sake of ease (and also to not share PHI), I made a shortened 27-row long example; this is what is shown in the screenshots. The data I'm working with is downloaded from a website we use to give people questionnaires. Sometimes, people do not have very straightforward answers to each question, so we type in "comments" in those cases to help clarify the exact answer participants gave. The data I'm working with lists the ID number we gave to each participant; the "variable" AKA the name of the question (tells me exactly which question it is in our questionnaire); which "session" AKA appointment the question is from (we repeat the questionnaire multiple times per participant throughout a year, some in-person, some over the phone); which coworker left the comment (commenter); and finally the actual comment itself. We are trying to see which questions/variables were given comments most often. This is a replica of what I have: https://preview.redd.it/9lvlnwplfgng1.png?width=1025&format=png&auto=webp&s=4716f993d500383af6307b8fb1724fef5b8626e1 This is what i WANT it to sort of look like: https://preview.redd.it/3d4w5a6ofgng1.png?width=980&format=png&auto=webp&s=dd32c658aceadd0afe5c427f0ec19177226d5081 In other words, I want: A count of how many times each type of variable repeats in the excel file. the dataset to be organized from which variable appears the MOST often at the top, and the ones that show up the LEAST at the bottom. to get rid of all rows that were for a "test" participant (notice the "test" rows 8, 18, and 26 in the first screenshot are gone in the second). to be able to find an exact question from the questionnaire website based on this sheet. for example, if I wanted to look more into the context behind row 5's comment, I would know to go to our questionnaire website, go to participant 111's questionnaires from the Lab 1 appointment and specifically look at the alcohol_amount1 question. In other words it is important to keep the participant number and session information. It doesn't need to look exactly like the second picture, that was just the first way to organize it that came to mind. As long as it fills the above requirements that all I need that's what matters. I was trying out pivot tables but I couldn't really get it to look in a way that made sense to me. I really don't know what else to do besides comb through all 3000+ rows one by one... sorry if any of this doesnt fit the exact posting rules. I tried. thanks for any help in advance🥹 submitted by /u/soupysyrup [link] [comments]
- Automate list size reductionI'm going to try to lay this out succinctly, let's see how I do: I am trying to build a flexible re-usable sheet to dump two reports into and receive a comparison report. One report has a list of date time stamps for a variable number of work orders (Dt), each with a variable number of entries. The second has a list of date time stamps for a variable number of inspections that also have work orders (Mt), but also have way more work orders, and often are missing inspections for some work orders entirely. What I want out the end is a report for each produced time stamp (Dt) that tells me how separate it is from the most recent (before or after) inspection (Mt). What I do right now is basically a min(abs(Dt-filter( (Mt)*(does Mt work order match Dt work order)))). It works, but oh em gee the computation, because every Dt entry gets compared to EVERY Mt computation. After that I just look at a pivot table of the results to see max time and average. This tells me if people went too long saving parts (Dt time stamp) but NOT measuring parts (Mt). Is there a way to take each of the original lists, have them pushed/pulled to a variable width series of columns (one per Work Order), then have a variable length comparator look at the first set of columns and do that "time to closest inspection" but only with the matching column from the second set of data? I know I could do it if I make them part of a data model and just slowly go through each work order, but I'd prefer it to just spit out the results at the end so I can hand this sheet off to others and not have to manually do the adjustments every day. Let me know if this isn't clear and I can try to set up some example mini-tables or something to better explain my gibber jabber above. e.t.a. uh Version 2603 (Build 19822.20150 Click-to-Run) submitted by /u/NeedIQMSHelp [link] [comments]