•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
What is the best tool or function to compare data between two workbooks where the data is almost identical and I need to find out what is different between them?
Our take
Comparing data between two workbooks can be a daunting task, especially when you're under time pressure to bid repair jobs. Fortunately, Excel offers powerful tools that simplify this process, allowing you to quickly identify changes between nearly identical lists. By leveraging features such as conditional formatting or the "Compare and Merge Workbooks" function, you can efficiently highlight additions and removals.
I am a regular Excel user, but I am not particularly well versed in it. I am frequently given a workbook of assets and all their associated data (size, model, manufacturer, last repair dates, ID numbers etc.), separated into columns. ID number is usually column A. These lists are anywhere from 50 to 400 rows. My job is to bid repair jobs from these lists. However, it never fails that just as I complete this, I am sent an "updated" list to work from. I then have to compare the two workbooks to see what, if anything, has been added or removed. The second list is often in more or less the same order as the first one, but not always. My question is how do I most easily compare the data from the two separate Excel workbooks to figure out what was added/removed? I will take any course or tutorial that can cut this down from taking me several hours to under an hour. I just need to know where I should be looking
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Compare tables and return values that aren't in both tablesSo I need regularly to compare lists of varying size and check whether there are new values in the new list compared to the old list and return them. I tried to come up with a formula that runs lightly by checking first how much data there is and then only use those rows and then returns only the values from the new list that are not in the old one. From now on, I only need to paste the data in the "New list" sheet and "Old list" sheet and it runs automatically. Question: can anyone think of a more efficient way to do this? =LET( OldList,TAKE('Old list'!B:B,COUNTA('Old list'!B:B)), NewList,TAKE('New list'!B:B,COUNTA('New list'!B:B)), FILTERNEW,IF(FILTER(NewList, ISNA(MATCH(NewList, OldList, 0)),"")=0,"",FILTER(NewList, ISNA(MATCH(NewList, OldList, 0)),"")), TAKE(FILTERNEW,COUNTA(FILTERNEW)) ) submitted by /u/Obvious-Cancel1727 [link] [comments]
- What's the best way to sort out differences between data sets?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 submitted by /u/SweetBusinesssss [link] [comments]
- Best way to crosscheck two Excel listsI have two Excel lists with data that need matching. One master list with object codes, the other a list with similar codes and details. Goal: Verify completeness an check which objects exist/match, flag missing/excess ones to see if the system has all relevant objects/data What is the best method for this task? I have almost no real excperinec in Excel submitted by /u/theophil93 [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#rows.com#Excel#workbooks#compare data#data comparison#repair jobs#assets#ID numbers#updated list#last repair dates#size#model#manufacturer