Copy rows with unique combination of columns, then sum

Options

Hi All,

Got a tricky one... I'm trying to harmonize data from one sheet to a new one. I've got data shuttle pumping data from a source Excel file in OneDrive to a raw data Smartsheet. I then want to copy the rows from the raw data sheet, but sum values in the target sheet into a single row if a combo of two columns matches. See below:

Source:

Desired Target:

Is this possible? Thanks in advance!

Best Answer

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭
    Answer ✓
    Options

    @Paul Higgins In that case, I think you'd need to prepopulate the Name & Week column on your destination sheet, then add a column with a cross-sheet reference formula to sum the hours.

    Raw Data sheet:

    Destination sheet:


    Formula:

    =SUM(COLLECT({Hours Range}, {Week Range}, =Week@row, {Name Range}, =Name@row))


    You can always add additional columns for other metrics as well.

«1

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    As far as I know, unfortunately, you cannot manipulate rows when copying (using workflows).

  • Paul Higgins
    Options

    Yeah, I am not using the automation to copy/move rows because of this. I was thinking more of some sort of match/index with a sumif?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Paul Higgins

    I hope you're well and safe!

    I think you should be able to structure a solution with the expression feature in Data Shuttle.

    Have you explored it?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    You could join the 2 columns you want to sum if they match in the original sheet, then use an index(collect(distinct combination with a counter for the index to return the joined columns to the target sheet. Then you can use sumifs to get your results.

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭
    edited 07/23/21
    Options

    What's your goal? Is it just to SUM each user's hours by week?

    If so, you can accomplish this many ways, and you wouldn't need to use copy row. The first way you could do this would be through reporting. The report functionality allows you to group columns, so in this case you'd either group by your user column or week column. You can then SUM the hours in the report. If you go this route, you'd want to make the column you group by the primary column. See example below:


    The other way to sum the users by week would be to setup a separate metric sheet that has a SUMIF formula looking into your raw data sheet. If you'd like to go that route, I can see about putting something together really quick.


    Hope that helps!

  • Paul Higgins
    Paul Higgins ✭✭✭
    edited 07/23/21
    Options

    @Andrée Starå - Thanks. I'll look into the expressions in Data Shuttle.

    @Ayelet Weiner - I've got some sheets that are summing the info, but it does it at the higher level. I actually need to report at the quarterly level by week, so I need the source info to already be summed up by week. I know I could do this in Reports too, but I need to do averages and things in the report also, so having small numbers at the week level throw off averages.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Paul Higgins

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭
    Answer ✓
    Options

    @Paul Higgins In that case, I think you'd need to prepopulate the Name & Week column on your destination sheet, then add a column with a cross-sheet reference formula to sum the hours.

    Raw Data sheet:

    Destination sheet:


    Formula:

    =SUM(COLLECT({Hours Range}, {Week Range}, =Week@row, {Name Range}, =Name@row))


    You can always add additional columns for other metrics as well.

  • Paul Higgins
    Options

    Thanks @Ayelet Weiner !

    I was trying to avoid the pre-population part, is there a way to pull all distinct row from another sheet? I created a helper column with a JOIN of the two other columns to simplify it.

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭
    edited 07/23/21
    Options

    Not to my knowledge. I'd either look into what Andrée suggested or maybe @Paul Newcome knows of a good solution / workaround.

    I was in a similar situation a few years ago where I had no choice but to setup a sheet that was pre-populated. I had about 80 Sales Reps and various KPI's I needed to track by week. It was a very robust sheet that was fully automated (based off fiscal week). I was even able to calculate 12 week rolling averages. Unfortunately, I no longer have access to that sheet, but I can probably help guide you on the structure / format if you have to go that route.

    Best of luck!

  • Paul Higgins
    Options

    @Ayelet Weiner Yeah, definitely a challenge. Thinking out of the box here, but maybe I can use the automation to copy the rows to the target sheet, then create a column that does the sumif, then pull those rows with a match on the first occurrence of each name/week combo. I'll look into it.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 07/23/21
    Options

    *this didn't work, I forgot distinct doesn't work cross sheet

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Paul Higgins I would suggest this...


    I would use an expression in Data Shuttle to go ahead and populate a column with a formula that will sum each row based on name/week. Since it is coming from Data Shuttle, we don't have to worry about missing rows by not using Column formulas, so we can build in a little bit of a filter here as well.

    =IF(COUNTIFS(Name$1:Name@row, Name@row, Week$1:Week@row, Week@row) = 1, SUMIFS(Hours:Hours, Name:Name, Name@row, Week:Week, Week@row))


    This will put the sum on the first row for duplicate name/week entries.


    Then you can pull a report that is aimed at this sheet and have it filtered to only show row where the Sum column is not blank.



    @L@123 DISTINCT should work across sheets. I noticed in your example you had

    index(collect(distinct


    Did you try this?

    index(distinct(collect


    With the COLLECT function inside of the DISTINCT?

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    @Paul Newcome I just tried that and it didn't work regretfully.


    you could also use a countifs with a dynamic range to count the first time the unique value appears, then use an index(collect()) to pull the values in based on the results. It would require a helper column, but you could get rid of your other helper column

    =if(countifs(name$1:name1,name@row,week$1:week1,week@row)>1,1,0)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @L@123 That's odd. I have a few sheets where I use DISTINCT with cross sheet references. More specifically JOIN(DISTINCT(COLLECT(