Easy Excel formula to extract values present in two lists
HTML-код
- Опубликовано: 8 сен 2024
- With Dynamic Array formulas we can easily extract values present in two lists. I also cover a formula by Oscar Cronquist for earlier versions of Excel.
Click here to download the Excel file and see step by step written instructions: www.myonlinetr...
Alternatively, you can use Power Query to compare two lists: www.myonlinetr...
Click here for the explanation of the legacy array formula for earlier versions of Excel: www.get-digita...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy
UNIQUE, SORT, FILTER...Awesome! Thank You 😀
Nice one Mynda! Thanks for sharing this technique :)) Thumbs up!!
Thanks for watching, Wayne!
Nice one!
Really helpful Thanks !!
More than Great Mynda ♥
Thanks, Hazem 😊
very useful tip 👏
i guess, Excel formulas will be same as in DAX soon - that will superb update
Not sure about being the same as DAX because they require a relational database, but there sure are some great new functions now that we have dynamic arrays.
Hi, Ms, MyOnlineTrainingHub, is there a way to combine index random formula
(Ex. =INDEX(Table2[Available Task],RANDBETWEEN(1,4))), with a condition of "sum of number of hours of available task" < 4 hours.
Which means, Randomly generate a daily schedule with certain possible task, and the sum numbers of hours must less than 4 hours.
Really sorry to putting out this question, cuz i really want to make a daily schedule randomizer but no idea how to do it. Do look forward for a solution from this wonderful teacher.
You could skip the >0 part in the CountIf formula as a count greater than zero would be considered as True...
=SORT(UNIQUE(FILTER(List1,COUNTIF(List2,List1))))
Too clever! Thanks for sharing :-)
@@MyOnlineTrainingHub My pleasure! :)
Was there a way to have Excel fetch other files (like Excel files) from specific folders on your computer to add to a data series/table? I thought one of your videos showed that but I can't find it :(
I cover getting files from a folder with Power Query here: www.myonlinetraininghub.com/power-query-get-files-from-a-folder
Hi Thanks a lot for all your videos! I have a question please. I have a master file that shows all product IDs in column A and their expiration date in column B, and I have a list of product IDs where I want to see the expiration date. I'm trying to pull the expiration date of these products using product IDs as a common key. With this formula you showed here, I can confirm whether the product IDs I'm looking for exist in the master file or not but I can't pull the expiration date. I hope my question makes sense. Please help!
Not sure why you wouldn't be able to use VLOOKUP/XLOOKUP/INDEX & MATCH to bring in the expiration date. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi ! Question for everyone ... I am using COUNTIF to generate a BOOLEAN array as the INCLUDE argument in a FILTER function. So, it looks like this : FILTER ( range A, COUNTIF ( range B, criteria ) > 0 ) . COUNTIF needs a range of values, meaning a real "physical" list of cells. COUNTIF cannot use an array as first argument. In my case, range B should be the result of a dynamic array formula, but then considered as a range so COUNTIF can do its job. Since COUNTIF cannot work with an array, I need to first spill it out and use SPILLED RANGE OPERATOR in the COUNTIF formula. Is there a formula so the dynamic array formula is considered as a range ? If this would be possible, then I don't need to spill it first. Hope you understood my problem.
Hi Sam, it's difficult to visualise, but you could try replacing the COUNTIF with a simple logical test; =FILTER(range, range B = criteria)
@@MyOnlineTrainingHub i know :-) . thanks for trying anyway. I extended my source data with Power Query - Merge Queries. Finally, preceeded FILTER with UNIQUE. That did the trick ! Looking forward to more video's .
Could you extend this technique to 3 or more lists?
Thanks.
Only if you're comparing to list 1.
Hi, thank you for all your videos, they are awesome. My question is about UNIQUE. I was really excited they finally added it because previously I had to use macros to have that functionality. Maybe it is just me but I can't see it there. Moreover, Microsoft says they plan to make it available in later releases. What version of Excel are you using?
Edit: support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
Look at the note
I'm using Office 365 on the Insider channel: insider.office.com/
I'm using office 365 but I don't have dynamic array functions ,,, anyone has a clue?
Hi Renas, It's available on the Insider Channel: insider.office.com/