Merge 2 column names from separate sheets into one in a single report

I want to create a single report that can be circulated on an email that pulls data from 2 separate sheets. In some cases I've been able to align the column headings, for example "Assigned To" is common across both sheets. However, in renaming that column, the resulting report no longer has an option to "Group" based on that value (all other columns are listed). I've refreshed, saved, exited, removed the sheet/re-added etc but the field is no longer visible. Why?

Also there is another field that I would like to group on but can't rename. In one sheet it is called "Due Date" and in the other sheet "End Date". It's like I want to put a "JOIN" formula in the Report so it just shows one column not two, and I don't think I can do this in either of the source sheets, or create a 3rd source sheet. There's no way to create a column "alias" is there so that you can "behind the scenes" call both the same thing?

Find more posts tagged with

Sheets

Reports

Accepted answers

All comments

Harvey E

Update - my theory for the first question is that the "Assigned To" value in one of the source sheets is allowing multiple entries. Ie the cells could have 2 names in that column. I'm guessing that the report can't show the same line twice (ie if the task is assigned to 2 people, show the task as 2 lines in a report, one for each assignee), and as a result it's not allowing the "Group by" for this column? Any ideas of a workaround (other than a single assignee!) ?

Thanks

MVP OPS

Hi Harvey,

In reports in order for data to flow correctly to each column you need to have the same column names and column types so getting to commonalities is the route. Helper columns will get you where you need to go.

For your first question, you could potentially creates helper columns that split multi cell contacts into their own columns. You would first create a helper column that is equal to your multi contact column, then create helper contact columns for person 1, person 2, person 3, etc. . Create an if(contains( formula in each helper contact to match the person.

=IF(CONTAINS("person a", [Persons assigned]@row), "personaemail@work.com")

Then group your report based on the the contact split helper columns.

For your second question, you could do similarly where you add a helper column that is named the column you want in your report, and have a formula in the helper equal to the date in the different named column.

Screen Shot 2022-05-16 at 6.36.15 AM.png

Hope this helps, let me know if you haven any other questions.

Brad

MVP OPS

Harvey E

Thanks for your help, which is useful but I don't think is the solution I really need.

The reason being that the team who use one of the sheets operate in "Card View" (Kanban style) and I don't believe you can hide columns from view when you open a card? ie when in Grid view you can hide columns you don't want cluttering the display but in Card view you can't. So adding a load of helper columns will really clutter up the display and take away from the usability. Any ideas here?

The only other way I though it could work is if you could "report on a report" - ie have a report that shows everything assigned to an individual, and then another report which pulls all of the individual reports back into one. Messy, and I don't think you can do it?

Thanks again!

MVP OPS

Hi Harvey,

Card view does add complexity, but With the new card view in reports you can surface to the team that operates in card view a report that has only the columns they need to see. My suggestion would be to create a separate card view report of the sheet for the card viewing team so they don't have columns displayed that aren't needed.

From my experience if you need one helper your probably going to need more in the future. As far as report on a report I don't believe that is an option as of yet.

Hopefully, some of this helps. Have a great day.

Brad

MVP OPS

lauren12345

I'm having the same issue - we use a report to view data from multiple sheets, and it would be great to be able to link 2 columns with similar names and the same data type within the report. I noticed that the report automatically did this for the Name column, but I'd love to be able to tell it that "Developer" and "Primary Technical Resource" should show in one column or to alias them on the report. I'm not able to rename the source columns since the sheets are shared across my org, but this would be a huge help to reduce the number of columns per report.

Paul Newcome

You can create helper columns in both sheets that have the same name and use that in your report.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.