•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
What's the best way to sort out differences between data sets?
Our take
Auditing a large inventory can be daunting, especially when your data is constantly evolving. As you navigate the complexities of comparing sheets with around 10,000 serial numbers, finding an efficient way to identify discrepancies is essential. The ideal approach involves using advanced filtering techniques that highlight items present in the old sheet but missing from the new one, without cluttering your results with duplicates. In this guide, we’ll explore effective methods that simplify this process, ensuring you can maintain accurate records effortlessly.
I have a large data set of an inventory with around 10,000 individual serial numbers that I'm currently auditing. We are continuously pulling from this inventory during the audit and my inventory will be a tad different next week compared to what it was when I exported the data this week.
What's the best way to sort out the differences between the sheet I pulled this week and the one I pull next week? I know how to filter out duplicates, but I'm wanting the new sheet to be able to say "x from old sheet isnt in new sheet" and not list it if that makes sense
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Manipulating large data sets in an Inventory management scenarioHello. I am an Inventory Lead and I can utilize the basics, and have googled this question and cannot figure out the best way to approach it. It may be way of thinking more than my application of my skills. Scenario: I have a large data set of adjustments made for a given time frame. Goal: to not manually count the number of adjustments per item (we have over 2k). And also manipulate that data to see: how much, locations, reasons. This data would then be used to focus in on certain areas to improve inventory accuracy. Is this an application of pivot tables that I am misunderstanding? I tried some videos and I couldn't seem to make it work. Im sure this is an easy answer. I just can't piece it together. Thank you all submitted by /u/eaglessuc [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]
- Any way to automate removal of older rows of data?I have a spreadsheet with 4 columns: first name, last name, score and date. I have people who are duplicated, some with the same date which I can easily remove with "remove duplicates" but I have examples where there are people with multiple rows from where they have taken a test a few years later, and i am trying to find a way to optimise my chopping up of this spreadsheet to only have a single row per user, and showing only their most recent score for the test. The date column is dd/mm/yyyy and then a 24 hour format time stamp and I can't think of a good way to optimise that as it covers multiple years. Theres no good consistency over the old date and the most recent date I imagine excel has some way of pruning older data. Atleast I hope so or ill have to check 50000+ rows manually to remove old results 😭 submitted by /u/Skellyhell2 [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]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#rows.com#Excel compatibility#large dataset processing#financial modeling with spreadsheets#data set#inventory#serial numbers#audit#differences#filter#duplicates#sheet#exported data#new sheet