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

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

Nice modification, Jim! Thanks for sharing.

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

Thanks so much for sharing, Jim! Shorter is always nicer 🙂 ...as long as it's not slower of course.