Need to look up a value in a whole column and return that value once

Our raw data has multiple rows for each project and package. We need to consolidate some of the columns to perform analysis. In addition, this is fed by data shuttle so we need to be able to use column forumlas.

If I can get help with the Project ID and Description, I should be able to figure out the rest.

Project ID: For this set of data, I just want to see "13651" once.

Description: We need to see "Fire Protection System" once and "Metal Building" once.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Afriesen

It sounds like a Report will be the best way to consolidate this data for you!

You can base the Report off of this one sheet. Then Group by the Project ID column, so you only see "13651" once. Add a secondary Grouping based on the Description column, so those headers are shown below "13651".

You can set Grouping to always show collapsed, so only the headers appear to start (and you can expand if you need more information).

Then you can Summarize a Report to show how many rows there are in each grouping.

User: "Screenshot 2022-09-05 at 13.33.53.png"

Reports will automatically bring in new rows that are added to the underlying sheet, so the content and summaries will be updated immediately.

Here's a free webinar on Grouping and Summarizing Reports: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

Afriesen

Thank you, @Genevieve P. However, I don't think this will get us where we need to be. You see a Project (Project ID) is made up of packages (Description), which will be bid out to multiple suppliers (Bidder). For this subset of data, there is 1 project, 2 packages, and 2 bidders in Fire Protection and 3 bidders in Metal Building.

Genevieve P.

Hi @Afriesen

Yes, exactly! The Report can quickly group and organize your data based on the Description and Bidder.

Then you can use the arrows to collapse/expand as needed. So if I only wanted to view Project 13651, I could expand those rows and collapse the other Projects:

Does that make more sense?

Cheers,

Genevieve

Afriesen

For dashboard purposes, I need to build charts that center around counts and the sum of Estimate. The reports so far are showing more packages than there really are. In the screenshots you are showing, we have 5 packages when in reality we only have 2. In addition, when you start doing summaries on the estimate field you are getting the sum of all 5 packages, when I just want to see the $ value once.

Genevieve P.

Hi @Afriesen

My apologies, I believe I understand now!

In this instance you can create cross-sheet formulas that count the number of DISTINCT values associated with each Project (Project ID).

You'll want to list the Project ID down one column of a second sheet. Then you can use a formula like this:

=COUNT(DISTINCT(COLLECT({Description Column}, {Project ID Column}, [Project ID]@row)))

This should return the number "2" in the instance you have detailed above. Then when you want to SUM, you can use the same sort of structure:

=SUM(DISTINCT(COLLECT({Column to Sum}, {Project ID Column}, [Project ID]@row)))

Let me know if that makes sense and achieves what you'd like.

Cheers,

Genevieve

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.