•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Compare tables and return values that aren't in both tables
Our take
Managing lists can be challenging, especially when you need to identify new values in varying data sets. This formula leverages the LET function to streamline the process of comparing old and new lists. By automating the comparison, you can simply paste your data into the "New list" and "Old list" sheets, allowing the spreadsheet to filter out values present in the new list but absent in the old one. If you have suggestions for optimizing this approach further, your insights would be greatly appreciated!
So 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))
)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Looking for a way to compare "Current" Column with "New" Column and remove any duplicates.https://preview.redd.it/3lw1tu3yovwg1.png?width=876&format=png&auto=webp&s=e4d76ab762d1e97338a006ba8aae3a8fb9bb34b2 Looking for a way to compare "Current" Column with "New" Column and remove any duplicates. I need to update incomes and hours for my data entry job and it'll be so much easier if any income and hours that hasn't changed be removed so it's less visual clutter for me... imagine this screenshot but with hundreds of more lines. It's very overwhelming and if the "New" columns match the "Current" columns then I don't even need to worry about it so they might as well be removed. submitted by /u/sadsadboy1994 [link] [comments]
- Excel Performance optimisation tips!Working in demand planning I have got it the point where I am making some pretty advanced files using a suite of techniques. My files often have lots of rows, with lots of Columns of complex formula including with sumifs, xloopup, ifs & Let. I’ve not advanced to using tables regularly though as I find the constraints & syntax annoying but am trying to get there & have started using power query to blend data for output analysis. The problem I am encountering is I filter ALOT drilling down into product groups etc, & excel tends to ‘hang’ a lot with ‘Not Responding’. Now I’m not sure it’s due to an underpowered machine (intel core i7 HP Elitebook) or, more likely lots of complex formula referencing ranges or tables. My question to the hive brain: share your optimisation tips & tricks! -Can Lamda combined with Let speed things up? -Are Tables vital to speeding up complex sumifs & lookups? - are match helper columns combined with Index leaner & faster than xlookup? Hit me with best tips & tricks! submitted by /u/NZGRAVELDAD [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]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
Tagged with
#Excel alternatives for data analysis#rows.com#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#AI formula generation techniques#Excel compatibility#financial modeling with spreadsheets#formula generator#Excel alternatives#compare lists#new values#old list