Create a dynamic unique count metric sheet for a leaderboard

pageella

In the site column I have:

=JOIN(COLLECT({Source Sheet: Gives Chapter Directory Range 2}, {Source Sheet: Gives Chapter Directory-Activity}, @cell = LARGE({Source Sheet: Gives Chapter Directory-Activity}, Position@row)), " ")

In the Activities column I have:

=INDEX({Source Sheet: Gives Chapter Directory-Activity}, MATCH(Site@row, {Source Sheet: Gives Chapter Directory Range 2}, 0))

And I am currently getting this........

Paul Newcome

Try this in the Activities column instead:

=INDEX(COLLECT({Source Sheet: Gives Chapter Directory-Activity}, {Source Sheet: Gives Chapter Directory Range 2}, CONTAINS(LEFT(Site@row, FIND(" ", Site@row) - 1), @cell)), 1)

pageella

I changed the deliminator back to '&' but this only works for the ones with & now

Paul Newcome

My apologies for not catching this sooner. I had a similar need come up just a few hours after my last comment. I put it in without thinking, but have no idea why I didn't do it here.

=INDEX(COLLECT({Source Sheet: Gives Chapter Directory-Activity}, {Source Sheet: Gives Chapter Directory Range 2}, CONTAINS(IFERROR(LEFT(Site@row, FIND(" ", Site@row) - 1), Site@row), @cell)), 1)

The error is actually coming from the LEFT function. Using the FIND to locate the delimiter outputs a zero. You can't pull 0 characters from the left of a string, so it errors out.

pageella

In the donations/activity column I have:

=INDEX({Source Sheet: Gives Chapter Directory-Activity}, MATCH(LEFT(Site@row, FIND("&", Site@row) - 1), {Source Sheet: Gives Chapter Directory Range 2}, 0))

In the Site column I have;

=JOIN(COLLECT({Source Sheet: Gives Chapter Directory Range 2}, {Source Sheet: Gives Chapter Directory-Activity}, @cell = LARGE({Source Sheet: Gives Chapter Directory-Activity}, Position@row)), "&")

Paul Newcome

You need the IFERROR around the LEFT function how I have bolded in my last comment.

pageella

Sorry I am confused which formulas I should be using now...

In the site column I changed to:

=INDEX(COLLECT({Source Sheet: Gives Chapter Directory-Activity}, {Source Sheet: Gives Chapter Directory Range 2}, CONTAINS(IFERROR(LEFT(Site@row, FIND(" ", Site@row) - 1), Site@row), @cell)), 1)

Paul Newcome

@pageella The Site column was fine with the JOIN/COLLECT. The INDEX/COLLECT that we made modifications for is used to bring in data related to each site and as such would go in Activities column.

pageella

Okay.. I started a fresh leaderboard because that one was getting confusing ha!

But it now works! Is there a way to get the same ranks to not repeat? e.g. EMA1 & LTN5 shows for both rank 3 and 4

Paul Newcome

Insert a new text/number column and use this:

=IFERROR(INDEX(DISTINCT(COLLECT(Site:Site, Site:Site, @cell <> "")), [Primary Column]@row), "")

Then you can use an INDEX/MATCH to reference the Activity count and bring over the appropriate count into the "final" table.

=IFERROR(INDEX([Activity Count]:[Activity Count], MATCH([New Column]@row, Site:Site, 0)), "")

pageella

That works! Thanks so much for all your help!!

Paul Newcome

Happy to help. 👍️