Compare Two Lists, Extract Customers Not in Both: Excel Formulas or Power Query; EMT 1811
Вставка
- Опубліковано 6 лип 2024
- Download Excel File: excelisfun.net/files/EMT1811....
Learn how to extract customer names who did not buy products from a specified list with worksheet formulas and Power Query.
Topics:
1. (00:00) Introduction
2. (00:41) Worksheet Formulas and Functions: FILTER, XMATCH, ISNA, AND, and COUNT.
3. (03:27) Formula Bonus #1: LAMBDA
4. (03:56) Formula Bonus #2: Use COUNT function to extract names when they bought no products, one or more products or all products
5. (04:35) Power Query using Group By feature and List.ContainsAny M Code function
6. () Summary, Closing, Video Links
I really appreciate the fact that it renews the video with the new excel function, we can see now "old way's versus new way's.
Glad you like this!!!!!
Thanks Mike. I will be watching this one again when I get back to my desk next week. Appreciate the video...
You are welcome as always, Wayne!!!! That desk is gunna be a lot of fun!!!
Super cool video!! ✌For fun, no lambda helper single cell:
=LET(c,B5:B20,p,C5:C20,n,E5:E6,u,UNIQUE(c),FILTER(u,ISNA(XMATCH(u,IF(XMATCH(p,n),c)))))
Duuuude!!!! Raaad!!! IF inside XMATCH : ) : ) : ) : ) I just put this in our download workbook : )
Another "no lambda" solution (but did you see Tae yong Shin's formula below?)...
=UNIQUE(FILTER(B5:B20,ISNA(XMATCH(B5:B20,FILTER(B5:B20,COUNTIF(E5:E6,C5:C20))))))
@@rickrothstein3681 Super cool solutions love them both ✌(myself, not an ..xxIFS user if I want to use the concept in a function for later use) 😉
@@rickrothstein3681 Dude!!!! Rick!!! Too funny: I was just simulating Matt's Inner Join/Left-Anti Join/Remove Duplicates Power Query solution (posted below) with worksheet formulas and came up with the exact silly formula as you: =UNIQUE(FILTER(B5:B20,ISNA(XMATCH(B5:B20,FILTER(B5:B20,COUNTIFS(E5:E6,C5:C20)))))).
@@rickrothstein3681 Yes, Tae's was solution was very "exactly once" lol
I seem to have to work with this type of setup a lot. I find FILTER(ISNA/ISNUMBER(XMATCH) my "go-to". However, it is interesting to see the algorithmic parallels between MAP and PQ in this extraction. Always great to have a cameo appearance by Sioux Radcoolinator!! . Glad she's still alive and kickin'!!! LOL
Yes!!! Sioux Radcoolinator is akways having fun : ) And yes, ISNA and ISNUMBER with XMATCH are as common in Excel solutions as water is to our daily life ; )
Boom!Super Neat Solutions...Thank You Mike :)
You are welcome, Bike & Boom Brother!!!!
Awesome Mike! Thanks!!
Yes, Wayne!!!!!!!!!!
Thank you Mike, 2023 has kicked off very nice... happy new 2023!!!
Happy New Year to you, Kulaava!!!!
Thanks
Happy new year to all.
Thank you Mike this is an excellent example to show on the use of spilled arrays and PQ.
You are welcome, Teammate!! But, wait : ) : ) : ) Thank you for the kind donation, Santosh!!!!!
@@msantosh1220 You are welcome for the spill and Power Query : ) : ) : ) Happy New Year to you.
Great video.
Glad you like it!!!!
Thanks Mike for this EXCELlent video.
You are welcome for the EXCELlence, Fellow Teacher : ) : )
Great, Mike.
Here the different logic =UNIQUE(TAKE(UNIQUE(HSTACK(B5:B20,COUNTIF(E5:E6,C5:C20))),,1),,1)
Wow!!! This has been an awesome video in that it inspired so many different formulas, each with a different beautiful logic : ) : ) Thanks for your great logic, Excel Wizard! I have added your formula to download workbook : )
@@excelisfun Thank you, Mike 😍
Unfortunately, nested UNIQUE also includes another "unique case" where some customers bought products ONLY from the list.
... I faced a similar problem trying to get by with the simplest possible manipulations :(
@@viktorasgolubevas2386 Thanks. That's right, I forgot to test that case.
So we need customer that Buy FILTER(B5:B20,COUNTIFS(E5:E6,C5:C20))
And all Unique customer UNIQUE(B5:B20)
Unique both exactly once return customer that not buy.
=UNIQUE(VSTACK(UNIQUE(B5:B20),FILTER(B5:B20,COUNTIFS(E5:E6,C5:C20))),,1)
@@ExcelWizard
Cool... and we get the solution provided by Tae yong Shin :)
This is how a team mindset works :) !
Very very cool Mike, thanks!!!!
You are welcome, Chris M!!!!!
What a neat solution!!
Glad you like them!!!!!!
Thanks Mike. I will try this the minute i ho to my comp. :):):)
Formula Guy!!!!!!
Tq so much Mike for this solution
You are welcome!!!
Awesome, just note that the # on the array @ 3:23 only works with Excel 365 version but other than that this is really helpful!
Fantastic
Glad you like this!!!!!!
Power query is an interesting method to perform and surely will provide result in a faster way
But I prefer the formula because it is an amalgamation of a bunch of whole new excel formulas
To be very TRUE in a small data I will use formula instead of power query
It helped me in one of my problem
Though the query is different but basically what I need to perform in my problem defining is solved in this video
One more amazing lesson learnt today
Thank you so much sir for the lesson for us "excel lover's "
Yes to awesome Excel Lover's, like you and the rest of the Team!!!!!!
I didn't know putting AND in front of a spilled array like that would work!
AND Logical test: one of the bedrock foundations of data analysis : )
Hi I just wanted to know that how much of your new book in excel covers out of a total of 700 m code language functions dealing with power query & dax functions numbering more than 250
My book is the only book to cover most tools in Excel and Power BI, so the sections on DAX and M Code cover the basics. But I do cover that foundation as completely as I can and discuss details that most other books do not. Covering all the functions is not what is important, it is covering the concepts and hows and whys together that give you the power to then do anything you want. That is what my book does.
Can you please make a video on the following:
I have an excel sheet with a list of Socials, next to them is a list of corresponding Employee ID numbers that go with each social. Here’s the problem, There are 10,000 plus socials and employee ID numbers. I have a separate list of Socials that will match most of those 10,000 plus socials but the new list of socials do not have employee ID numbers attached to them. What I want to do is match the short list of socials with the big list of socials, and copy the corresponding Employee ID numbers and place them (copy them) next to the short list of Socials.
End result would be that I find the employee ID numbers for the short list of Social Security numbers. I don’t want the result to just say there is a match,I want the employee ID numbers to copy next to the new matching SS numbers.
Compare two table sale and purchase and find uniqe stock record by formula which not in purchase record
Pls gide any formula for excel 7
Record search by only barcode no. Uniq code
If function give error or wrong record
=UNIQUE(VSTACK(UNIQUE(B5:B20), FILTER(B5:B20, COUNTIF(E5:E6, C5:C20))), , TRUE)
Very cool distinct formula ; ) I added it to download.
unfortunately, some functions are not available in MS. Office 2019
None of these formulas are. Only in Microsoft 365. Excel 2019 does not even allow spilled array formulas. My work still uses 2019 on most computers and I cannot build solutions for most people. Although many entities do not have M 365 yet, I wish they would soon - because it is just so much easier with the new functions and dynamic spilled array formulas. Are you in a job that issues 2019 to everyone (like I am) or is this your personal version? If it is, it is worth the cost to get M 365 by 100 miles : )
@@excelisfun thanks for your kind response. The issue is on my personal PC. But I use most alternative such as vba codes, vba functions and array formula for my work. This is worth to mention that I use kutools App for Excel.
I can't say thank you enough.
@@sebghatulbarykhabaey6680 You are welcome for the teachings!!!
@@excelisfun At last eventually today I found a free version of Microsoft Office 365.
@@sebghatulbarykhabaey6680 Free? How? : )
The COUNT + XMATCH is my favourite.
Since there are no operations on the original data, one can use the COUNTIFS function too.
=LET(
_c, B5:B20,
_p, C5:C20,
_db, E5:E6,
_uc, UNIQUE(_c),
_tf, BYROW(COUNTIFS(_c, _uc, _p, TOROW(_db)), LAMBDA(a, OR(a))),
_r, FILTER(_uc, 1 - _tf),
_r
)
Can't think better in Power Query.
Great formula rendition!!!! I have added it to the download file : ) : ) : )
=LET(
a,B5:B20,
u,UNIQUE(a),
FILTER(u,MAP(u,LAMBDA(b,
AND(NOT(TOROW(E5:E6)=FILTER(C5:C20,a=b)))))))
Opps... I said originally : "That is similar to the cone I showed in video : )". But that is not correct. I miss-read your formula. Your formula is very different than the one I did : ) : ) : )
I added your to download : )
Opps... I said originally : "That is similar to the cone I showed in video : )". But that is not correct. I miss-read your formula. Your formula is very different than the one I did : ) : ) : )
I added your to download : )