What if I wanted to vstack both of those list together and have a seperate column that identifies the matches from the non matches? That way you can see everything in one place 😊
You could do that absolutely. Wrap a HSTACK around the VSTACK to add the extra column. Use LET to store the stacked tables virtually for COUNTIFS to work off.
Great! What about how to extract all records, between dates and if they match 1 or two more criteria, from two or more worksheets, into a single master sheet, and organize all those records, by date, with duplicates, and triplicates, and without blanks? And just with formulas. If you know how, and if you could be so kind as to share it, I would massively appreciated it!! 🙏🙏 Thanks
Thanks, Tracey. No major reason, I just don't use COUNTIF anymore as it offers nothing over COUNTIFS. So easier to use one rather than switch between both (especially with SUMIF/SUMIFS due to the difference).
No! You can use normal ranges such as B2:B10, however, those names are from Tables and it makes the formula fully dynamic. If more rows are added to either table everything immediately updates.
Very difficult using formulas without the dynamic arrays. Involves awkward formulas or helper columns. Power Query approach would be best www.linkedin.com/pulse/compare-two-lists-excel-differences-matches-alan-murray/
Learn more than 150 of the best Excel functions with 'Advanced Excel Formulas'
amzn.to/3Rg87Go
What if I wanted to vstack both of those list together and have a seperate column that identifies the matches from the non matches? That way you can see everything in one place 😊
You could do that absolutely. Wrap a HSTACK around the VSTACK to add the extra column. Use LET to store the stacked tables virtually for COUNTIFS to work off.
Great!
What about how to extract all records, between dates and if they match 1 or two more criteria, from two or more worksheets, into a single master sheet, and organize all those records, by date, with duplicates, and triplicates, and without blanks?
And just with formulas.
If you know how, and if you could be so kind as to share it, I would massively appreciated it!! 🙏🙏
Thanks
An alternative to NOT(COUNTIFS(...) is 1-COUNTIFS(...). Have you known it, perhaps ?
Sure! That will work. Any number except 0 evaluates to TRUE. Thanks for the tip Romulus 👍
Nice. How do you avoid 0's when using both tables and not in both if both tables have a blank row in them without just adding @""
Thank you! If they both have a blank, FILTER will not return it.
Nice alternative to some other methods, I have used (usually query editor). Is there a reason you use countifs and not just countif
Thanks, Tracey. No major reason, I just don't use COUNTIF anymore as it offers nothing over COUNTIFS. So easier to use one rather than switch between both (especially with SUMIF/SUMIFS due to the difference).
Frist comments 22.41 pm IST
Do you have to define Names first?
No! You can use normal ranges such as B2:B10, however, those names are from Tables and it makes the formula fully dynamic. If more rows are added to either table everything immediately updates.
Thank you Alan for this vreat video 📹 👍
You are very welcome. Thank you, Nader.
ID 219 is not in 2nd table but shows a count of 1?
The 1 corresponds to the first table. So to ID 483.
How to do this in excel 2019?
Very difficult using formulas without the dynamic arrays. Involves awkward formulas or helper columns. Power Query approach would be best www.linkedin.com/pulse/compare-two-lists-excel-differences-matches-alan-murray/
Thank you for your sharing!
No worries, my friend.
Such a brilliant mind wow, I would never have thought of that, just genius!
Thank you! I hope it helps.
Brilliant explanation Alan! Thank you very much!!!
My pleasure! Thank you, Ivan.
greaaaat my friend
Thank you 😊
Super! Good job!
Thank you very much!
Excellent, thanks for the lesson!
My pleasure!
Quite a useful thing, thank you
Glad it was helpful!
Excellent. Thanks Alan 🌹
Many thanks Kebin.