Log In
Register
Home
June 25, 2020
Offline
I have two tables of Broker queries, is it possible to extract a single dynamic list of distinct Brokers without recourse to Power Query?
eg if table1 had Broker1 and Broker2, table2 had Broker2 and Broker3 then I would like to be able to produce a list of Broker1, Broker2 and Broker3 which doesn't need a Refresh to update it
I was hoping something like UNIQUE({table1[Broker];table2[Broker]}) would work (ok with constants but not references)
Kind regards,
jim
See Answer
July 16, 2010
Offline
Hi Jim,
You can use this formula by fellow Excel MVP, Ismael Romero:
=UNIQUE(LET( ranges, (A2:A4,C2:C4,E2:E4), areas, AREAS((A2:A4,C2:C4,E2:E4)), rows, ROWS(A2:A4), TotalRows, SEQUENCE(areas*rows), NumArea, CEILING.MATH(SEQUENCE(areas*rows),rows)/rows, Row, TotalRows-(NumArea-1)*rows, INDEX(ranges,Row,1,NumArea))) See file attached. Mynda
June 25, 2020
Offline
Thanks Mynda,
I knew there would be a straightforward answer 😉
This only seems to work if there are the same number of entries in each list and isn't dynamic for new additions
I won't pretend to know exactly how the above works, but I've modified it a little:
each list of brokers is now a table (tA, tB and tC), I've renamed some of the names used (not keen on them being the same as functions) and introduced an error trap for unequal list lengths
I'm sure Ismael would come up with a much better solution but this formula now works perfectly for my needs:
=UNIQUE(LET(
rRanges, (tA[Broker],tB[Broker],tC[Broker]),
cAreas, AREAS(rRanges),
cRows, MAX(ROWS(tA),ROWS(tB),ROWS(tC)),
TotalRows, SEQUENCE(cAreas*cRows),
NumArea, CEILING.MATH(SEQUENCE(cAreas*cRows),cRows)/cRows,
cRow, TotalRows-(NumArea-1)*cRows,
IFERROR(INDEX(rRanges,cRow,,NumArea),INDEX(tA,1,1))))
thank you both for your time and attention
jim
PS without the LET function, this expands to a nightmarish:
=UNIQUE(IFERROR(INDEX((tA[Broker],tB[Broker],tC[Broker]),SEQUENCE(AREAS((tA[Broker],tB[Broker],tC[Broker]))*MAX(ROWS(tA),ROWS(tB),ROWS(tC)))-(CEILING.MATH(SEQUENCE(AREAS((tA[Broker],tB[Broker],tC[Broker]))*MAX(ROWS(tA),ROWS(tB),ROWS(tC))),MAX(ROWS(tA),ROWS(tB),ROWS(tC)))/MAX(ROWS(tA),ROWS(tB),ROWS(tC))-1)*MAX(ROWS(tA),ROWS(tB),ROWS(tC)),,CEILING.MATH(SEQUENCE(AREAS((tA[Broker],tB[Broker],tC[Broker]))*MAX(ROWS(tA),ROWS(tB),ROWS(tC))),MAX(ROWS(tA),ROWS(tB),ROWS(tC)))/MAX(ROWS(tA),ROWS(tB),ROWS(tC))),INDEX(tA,1,1)))
named ranges could be used but then it wouldn't be as portable, so I'm very grateful for LET
July 16, 2010
Offline
Nice modification, Jim! Thanks for sharing.
June 25, 2020
Offline
I found Ismael's post and the linked source article by Mourad Louha which laid out the logic behind it and, now that I understand it a little better, I've found a simpler solution
NB this only works for 2 sources - which suits my requirements but, if required for more than 2, then it can be extended but I think I'd use the solution above for more than 3, which uses the "area" version of INDEX (and which I still don't totally follow)
I've also tried to use better names and spacing to ease understanding (the two original tables are now tabA and tabB)
=UNIQUE(LET( datA, tabA[Broker], datB, tabB[Broker],
numA, ROWS(datA), numB, ROWS(datB),
seqR, SEQUENCE(numA+numB),
IF(seqR<=numA, INDEX(datA,), INDEX(datB,seqR-numA))))
without LET, this is much simpler than before:
=UNIQUE(IF(SEQUENCE(ROWS(tabA)+ROWS(tabB)) <= ROWS(tabA),
INDEX(tabA[Broker],),
INDEX(tabB[Broker], SEQUENCE(ROWS(tabA)+ROWS(tabB),,1-ROWS(tabA)))))
jim
Answers Post
1 Guest(s)
