I am trying to create a "level of effort" calculation where we compare the count of bidded, lost, and awarded projects against a specific criteria, which is often a dropdown column in the source sheet. I have tried using JOIN, COLLECT, and INDEX from at least a dozen posts in the Community but nothing has worked to date. For example, one of the "level of effort" calculations we're looking at compare the amount of bidded, lost, and awarded projects to the client account/name. We have three sheets that have identical column setup: Active, Closed, and Complete/Awarded. * I need to combine the client list in a dynamic array from these three sheets. Once this has been combined, I only need the unique values from this list. In excel, the equivalent function is "UNIQUE(VSTACK())". I need to AVOID entering this in manually, as the client list is updated multiple times a day. I am unable to use a reference number/ID column because some projects are bid to multiple clients—I need a record of how many projects have been bid to a specific client independent of if other clients received the proposal. * Alternatively, if there were a way to utilize column linking using the Table view in the source cells within the described formula that would solve my issue, I believe, but this is not possible to my knowledge. I am hoping there is a way to do this to prevent near-constant babysitting.

How do you create a dynamic list of unique values from multiple sheets?

I am trying to create a "level of effort" calculation where we compare the count of bidded, lost, and awarded projects against a specific criteria, which is often a dropdown column in the source sheet. I have tried using JOIN, COLLECT, and INDEX from at least a dozen posts in the Community but nothing has worked to date.

For example, one of the "level of effort" calculations we're looking at compare the amount of bidded, lost, and awarded projects to the client account/name. We have three sheets that have identical column setup: Active, Closed, and Complete/Awarded.

I need to combine the client list in a dynamic array from these three sheets. Once this has been combined, I only need the unique values from this list. In excel, the equivalent function is "UNIQUE(VSTACK())". I need to AVOID entering this in manually, as the client list is updated multiple times a day. I am unable to use a reference number/ID column because some projects are bid to multiple clients—I need a record of how many projects have been bid to a specific client independent of if other clients received the proposal.
Alternatively, if there were a way to utilize column linking using the Table view in the source cells within the described formula that would solve my issue, I believe, but this is not possible to my knowledge.

I am hoping there is a way to do this to prevent near-constant babysitting.

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Cell linking

Accepted answers

All comments

Paul Newcome

First insert a text/number column called "Number" and manually enter the numbers 1 through however many you think you will need (plus a buffer).

Then you will have three text/number columns called "Active", "Closed", and "Complete". Each of these will contain similar formulas to bring over the unique list from each of the three sheet. These three columns should also stay right next to each other throughout.

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Client Name}, {Source Sheet Client Name}, @cell <> "")), Number@row), "")

Once you have that in each of the three "source sheet" columns, your next column over would use a very similar formula pulling the distinct entires from each of these three columns.

=IFERROR(INDEX(DISTINCT(COLLECT(Active:Complete, Active:Complete, @cell <> "")), Number@row), "")

MHumphrey

Hey Paul,

Thank you for taking a look at this—that won't quite work in the case of multiple clients associated with a project. Here's an example:

Let's say that this is the "Active Projects" sheet:

This is what the result needs to look like using "COUNTIFS":

Now, I need to combine the source information from all three sheets: "Active, Closed, and Complete".

There are many, many statuses we're looking at (10+), and ~185 clients we're cross-referencing. The proposed solution as I understand it, does not allow us to use a multi-select dropdown source column lest the 'client name' be "CLIENTACLIENTB" when JOIN is used.

I need to create a dynamic client list either from a linked column or using the equivalent of "VSTACK".

Paul Newcome

My apologies. I didn't see in your original post where you mentioned that the Client column was a multi-select. That does change things.

How many clients could be listed on a single project?

MHumphrey

The most I've ever seen is 6—though it could be up to 10 (this would be very unusual).

Paul Newcome

In that case, I would suggest eleven helper columns on the source sheets to parse the clients out.

The first one (called "String" in this example) creates a string with special characters to make parsing easier.

="!" + SUBSTITUTE([Client Name]@row, CHAR(10), "!") + "!"

The next ten (one for each client) would follow this syntax for the first one:

=IFERROR(MID(String@row, FIND("~", SUBSTITUTE(String@row, "!", "~", 1)) + 1, FIND("~", SUBSTITUTE(String@row, "!", "~", 2)) - (FIND("~", SUBSTITUTE(String@row, "!", "~", 1)) + 1)), "")

In this, you will notice 3 SUBSTITUTE functions that specify a number at the end.

SUBSTITUTE(String@row, "!", "~", #)

That # is the key to modifying the formula to pull the second, third, fourth, etc. entries out of the multi-select.

If we look at just the numbers inside of the SUBSTITUTE functions, we will see that they go (in order of left to right) 1/2/1. To pull the second, we change these to 2/3/2. The third entry can be extracted using 3/4/3, so on and so forth.

Second:

=IFERROR(MID(String@row, FIND("~", SUBSTITUTE(String@row, "!", "~", 2)) + 1, FIND("~", SUBSTITUTE(String@row, "!", "~", 3)) - (FIND("~", SUBSTITUTE(String@row, "!", "~", 2)) + 1)), "")

Third:

=IFERROR(MID(String@row, FIND("~", SUBSTITUTE(String@row , "!", "~", 3)) + 1, FIND("~", SUBSTITUTE(String@row, "!", "~", 4)) - (FIND("~", SUBSTITUTE(String@row, "!", "~", 3)) + 1)), "")

Then you would use the method I mentioned above except the ranges in the first formula would cover each of the ten client helper columns in the source sheet:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Client Helper Columns}, {Source Sheet Client Helper Columns}, @cell <> "")), Number@row), "")

MHumphrey

I appreciate your insight and suggestion, but is there truly no clean way to do this?

That is not efficient in the slightest.

I recently asked a question about equivalent functions like Excel's LAMBDA and Name Manager—and there is no solution to that either.

TLDR; So, I have to create 11 helper columns in each of the 3 source sheets and rebuild the references accordingly?

How else do people track the performance of ever-changing criteria without and excessive amount of manual entry?

Paul Newcome

You could leverage the API, the add-on Bridge, or other similar third party apps.

ccarriger

@MHumphrey, I've done something similar to what you're describing but it is trickier when dealing with a multi-select since you have to query the cells based on each potential value (client in your case).

What I would recommend is leveraging the new Linked Dropdown List feature as your source of client names.

Create a Client Sheet
Make the primary column your Client column—as new clients are added, you would add their name in a new row here.
In your Active Projects sheet, have the Client Name column link back to the Client column in the Client sheet—follow the instructions here if you haven't already used this feature. (And you can do this across multiple sheets if your client names should be selected in more than just this sheet.)
1. Make sure you select the multiple values option in the column settings—the linked dropdown will default to single selection when you select your source, so you'll have to toggle that back to multiple values.
Back in the Client sheet, set up your columns like Proposal Submitted (and the rest).
Now you get to use your CountIfs formulas with a slight twist…
=COUNTIFS({Client Name Range}, CONTAINS([Client]@row , @cell ), {Proposal Status Range}, @cell = "Submitted")

Using CONTAINS allows you to query a multiselect column and return any instance where the Client Name exists within it. I assumed that you might have proposal statuses or another column of criteria to check so I included that but just switch that to any additional criteria that should be checked or just get rid of it entirely if it's not needed.
You can then generate a report that pulls all that data from the Client sheet and filter out anyone who has 0 submitted proposals or another data point that shows they're not needing to be reported on. If you don't have an existing data point, then I'd recommend adding a Publish column on the sheet—make it a checkbox and toggle it on or off if you have clients who don't need to be reported on.

I know this doesn't allow for your client name list to be purely dynamic but aside from putting the name in that one column to feed through to the rest of your sheets, you don't have to manage the client names. And if you use my advice on having a Publish column to show clients who should be visible for reporting, you can get more advanced and leverage that field create a filtered lookup for your Client Name column.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.