Hello,
I've made an excel file to illustrate what im trying to get.
In the file I have two columns, Accounts and Type. Some of these accounts are both A and B and some are only A or B.
In column C, I would like to create a list of the accounts that contain both A and B.
Is that possible to do?
/Peter
Try:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
C2=UNIQUE(FILTER(FILTER(A2:A29,B2:B29="a"),ISNUMBER(MATCH(FILTER(A2:A29,B2:B29="a"),FILTER(A2:A29,B2:B29="b"),0))))
or (if you have moved on from Version 2202):
=LET(A,A2:A29,B,B2:B29,UNIQUE(FILTER(A,BYROW(COUNTIFS(A,A,B,{"A","B"}),LAMBDA(x,SUM(x)))>1)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
@Glenn: I think there may be a flaw in your logic. If, for example, you change all the As against Stockholm to Cs, Stockholm is still included in the list.
Re: Retrieving cells that contains two values from a list
with Power Query
Account List Tokyo B,A Stockholm A,B Berlin A,B Helsinki B,A Munich B,A
Please Login or Register to view this content.
It's not my day, is it???
Fixable with:
=LET(A,A2:A29,B,B2:B29,UNIQUE(FILTER(A,BYROW(COUNTIFS(A,A,B,{"A","B"}),LAMBDA(x,IFERROR(SUM(1/(1/x)),0)))>1)))
Last edited by Glenn Kennedy; 03-18-2023 at 10:51 AM.
Yay, mended! I don't understand it, but it works. I tidied my formula up, but it's still the longest solution. The advantage of it is, I understand it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks