Easy Excel formula to extract values present in two lists - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 4
Sam
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.
reply
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.
reply
eiksoo
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.
reply
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.
reply
kim
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!
reply
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!
reply
Tom
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 :(
reply
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 :(
reply
Add a review, comment
Other channel videos
7:32
