Get Unique Record Pairings

I have a sheet that tracks the name of sub-contractors with the name of their business, and a column indicating the agency that sub-contractor works with. A single sub-contractor, Joe, from ABC Company, may work with Agency 123 and Agency 789, while Sally from ABC Company may work with Agency 567.

What I want to get to is a single row for ABC Company that shows me Agency 123, Agency 789 and Agency 567. I don't have the Pivot App, which seems like it would be the easiest way to do this. I did create a report grouped by sub-contractor business name, which sort of gets me what I need but there are multiple rows with the same data instead of a single row for each.

I'm not against creating some sort of grid with my sub-contractor business names as the Y axis, and the agency names as the X axis, with an X or something indicating if the sub-contractor business has any row with that agency's name in it, but before I go down that road I wanted to see if anybody had an ideas.

Find more posts tagged with

Accepted answers

All comments

Dale Murphy

@Bethany Garcia How many sub-contractors and how many agencies do you have to track? If the number is very limited for one or the other, than a matrix using columns for the piece that has only a few options (that don't change!) could work. Otherwise, the idea of a single row to represent several rows is problematic.

Bethany Garcia

@Dale Murphy - I have 17 agencies and 54 subcontractors. The numbers could change over time, but not often.

Hollie Green

If you are wanting to see something like the below I believe the following index collect formula will work. IfError(Index(Collect({Source Sheet Agency Name Column},{Source sheet business name column},[Business Name]@row,{Source Sheet Agency Name Column},"Agency 123"),1),"") You would need to change the formula under each Agency Name to match the corresponding Agency. You could then turn it into a column formula so you would only need to enter it on the top row of your Target sheet.

Dale Murphy

@Bethany Garcia While we are exploring ideas, what about a multiselect dropdown column for the agencies? You could have a (single) row for each sub, and select all the agencies they work with in a single column.

Or you could have a few agency columns, maybe by a classification like Primary, Secondary, ...

Bethany Garcia

@Dale Murphy that's what I want to get to, is a single row for a subconractor with all of the agencies listed, even as multi-select. What I'm starting from is a sheet that contains a row for each of the subcontractors themselves, with a column for the name of their business (the subcontractor business), and then the agency dropdown where we indicate which agencies that individual person works with. I'm then trying to take that data to get just the subcontractor businesses with all of the agencies listed for them throughout the entire sheet.

Dale Murphy

@Bethany Garcia Ah. So you want to collapse your view down to the contractor business level, not the subs that work for each of those subcontractors. Got it.

Something @Paul Newcome suggested to @Judith Campf might help. First you need a second sheet to hold the results of a JOIN query. That sheet should have (at least) subcontractor business name in a column and a column for the agencies:

In this new sheet, in the Agencies column, use:

=JOIN(COLLECT({Range in original sheet w AGENCY names}, {Range in original sheet w subcontractor business name}, [Subcontractor business in new sheet]@row), " ")

You should get all the agencies for a given sub-business, separated by a space (the character between the double quotes). They won't wrap nicely, just beware.

Bethany Garcia

@Hollie Green this almost works perfectly - the agency name column in my source sheet is a multi-select column, because a given subcontractor may work for more than one agency. Those with more than one value in the agency column aren't appearing on the grid using the formula you provided above. Is there a modification I can make to the formula to look for the row to just contain the value instead of looking for an exact match? Or will that formula just not work with a multi-select column?

Hollie Green

The only other way I can figure out how to do it is create a helper column beside each business name that you can hide with a Join/Collect Forumula - Make it a muli select column

=Join(Collect({Source Sheet Agency Name},{Souce Sheet Business Name},[Business Name]@row),Char(10))

This will bring in all of the agency names that show up for that particular business into one cell on your destination sheet.

Now for each of your columns to show just a single agency name beside the business

=If(Contains("Agency 123",[helper column]@row,"Agency 123","")

You would put the above formula changing the Agency Name you are searching for in each column

Another thought so you don't have to change the Agency Name each time is to make your top row also list the agency name and use the below formula

=If(Contains([Agency 123]$1,$[helper column]@row,[Agency 123]$1,"")

You wouldn't be able to convert to column formula but could drag across and down

It would depend of if it is easier for you to have that extra row at the top or have a column formula that you had to change for each 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.