Join and Group nightmare

[SyxRyx]

Hi

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!

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

[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? 

[BrandonB]

I figured this was the only logic way to account for missing names. Here is the solution and workflow is attached:

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