Create a dynamic unique count metric sheet for a leaderboard
Answers
-
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........
-
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)
-
I changed the deliminator back to '&' but this only works for the ones with & now
-
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.
-
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)), "&")
-
You need the IFERROR around the LEFT function how I have bolded in my last comment.
-
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)
-
@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.
-
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
-
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)), "")
-
That works! Thanks so much for all your help!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!