Copy rows with unique combination of columns, then sum

Paul Higgins · 07/23/21

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!

Ayelet Weiner · 07/23/21

@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.

Sameer Karkhanis · 07/23/21

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

Paul Higgins · 07/23/21

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å · 07/23/21

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!

L_123 · 07/23/21

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 · 07/23/21

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 · 07/23/21

@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å · 07/23/21

@Paul Higgins

Happy to help!

Ayelet Weiner · 07/23/21

@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 · 07/23/21

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 · 07/23/21

https://community.smartsheet.com/discussion/comment/298913#Comment_298913

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 · 07/23/21

@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 · 07/23/21

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

Paul Newcome · 07/23/21

@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 · 07/23/21

@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 · 07/26/21

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

Copy rows with unique combination of columns, then sum

Best Answer

Answers

Categories