Join and Group nightmare

I've been breaking my head with a problem which looks quite simple and yet, I just keep running around in circle and can't get my workflow to work. I am an average, not advanced user.

I have the following data sample (real data is several thousand lines by 32 columns)

Each [Project #] is unique. I want to get the total sales by Project Group, like this:

The problem is the data is incomplete. Here are the issues:

Green: I need to sum the sales but retain the Project # and Name corresponding to the Project Group. Not first or last or whatever, but the one matching the Group Name.

Grey: The project # corresponding to the Project Group by does not exist, I need to replace those Project # by the Project Group and sum.

Amber: Project 100-09 belongs to a group but the info is missing. I need to change to Project Group to 100-09 before grouping

The rest are projects that don't belong to a group and need to be left a they are

So if anyone wants to take a crack at this, I'd really appreciate the help!

Developer Tools

Accepted answers

All comments

BrandonB

This workflow will solve it for you with the exception of 100-05 which does not have a project number/name combo in your initial data. Workflow is attached.

Matching projects.yxmd

SyxRyx

Thanks BrandonB

That does not work. The 100-09 group is still split and the missing 100-05 is still missing. The easy matching and summarizing I can do... It's taking care of the exeptions that I can't figure out.

BrandonB

I was able to handle the logic for the 100-09 group by taking the records with a null project group themselves, but match to project groups of others to be bundled in:

I haven't included logic for the missing name of 100-05 yet because I'm not sure how you want it handled. From your original data set, 100-04 and 100-06 are both in group 100-05 and it looks like you desire the name to be DDDD which corresponds with 100-04. In this type of scenario do you just want the first name from the project numbers in the group?

SyxRyx

Nice trick for the 100-09. Thanks.

For the 100-05 case, I will most likely want to use some sorting and take the first occurance of the sorted subgroup (Top Sales, for example).

Really appreciated

BrandonB

Perfect, so my V3 example attached above should be ideal. You could do additional sorting in the workflow to make the first occurrence of the name reflect your ideal sorting scenario by adding a sort prior to the summarize on the bottom part of the workflow.

SyxRyx

Thanks. I'll test this with real data and see if all cases are handled.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335