Create a list from check marks in a grid

Matthew Barrett

I need to pull data from a grid organized like this (with yellow items being being column headings):

into a list that is structured like:

I am stumped on how to do it. I do not own the original data so I cannot restructure it into a different form.

Any tips?

-Matthew

Genevieve P.

Hi @Matthew Barrett

You could use a cross-sheet JOIN(COLLECT formula to Join together in one cell all of the different Teams associated with each Resource, but the formula wouldn't be able to parse the details into multiple rows.

Here's an example of what that might look like:

Note that for each Resource you'll need to create a unique cross-sheet reference to that specific column. In my formula I'm presuming that your Resource columns are checkbox columns, where 1 = a checked box.

Here's the formula structure:

=JOIN(COLLECT({Team}, {Resource 1}, 1), ", ")

Will this work for you?

Cheers,

Genevieve

Matthew Barrett

Unfortunately I need the pulled data in multiple rows so that I can use that to pull from yet another set of data in order to create a report.

Devin Lee

Allow for blank answers and just filter them out.

=IF([Resource 1]1 = 1, Team1, "")

=IF([Resource 1]2 = 1, Team2, "")

=IF([Resource 1]3 = 1, Team3, "")

etc

=IF([Resource 2]1 = 1, Team1, "")

=IF([Resource 2]2 = 1, Team2, "")

=IF([Resource 2]3= 1, Team3, "")

etc

ETC

Matthew Barrett

The source data is much more complicated than I presented. There are thousands of "teams" and a dozen or so "resources". So far for each resource I have a sheet that pulls a list of teams using Index and Collect.

This gives me a list of teams I can reference for the metrics associated with each resource but does not allow me to build the metrics by team.