Aug 26 2021 08:01 AM
Aug 26 2021 08:01 AM
I have a data set where a "project" is identified by a number and fiscal year. (xxxx-yy). Each project, however, may have multiple "departments" involved or may have two or more people identified within the same department. Data set looks something like:
1234-17 - John Smith - chemistry - $10,000
1234-17 - Ted Jones - engineering - $5,000
2345-17 - Fred Adams - Math - $100,000
2233-17 - Janet Smith - education - $50,000
2233 -17 - Andy Taylor - education - $5,000
workbook is about 5000 rows and what I want to do is identify the total dollars associated with interdisciplinary work. So I would want to count 1234-17 as $15,000 because two people crossing departmental lines are collaborating on the same project. I would NOT want to count 2345-17 because Fred is a loner. AND I would not want to count 2233-17 because Janet and Andy are in the SAME department so while they are collaborating, it is not interdisciplinary per se. My excel skills are kind of weak and I have tried sorts and subtotals and all kinds of wonky things but cant seem to find an easy solution. Some general guidance of how to figure this out without going line by line and deleting stuff I dont need would be great. This is the 21 century afterall. Thanks much.
Aug 26 2021 08:57 AM - edited Aug 26 2021 08:58 AM
@taylomm Your example didn't reveal how your data really looks like, but I assume you have it in a tabular format. I'm not a big fan of complicated formulae if I can solve it with PowerQuery. The attached file demonstrates just that. Easy to learn and easy to maintain. And it easily takes 5000 rows of data. No problem.
It takes the data, groups it and checks if a project has more than one department involved. Then, it sums the amounts. The result is shown in the green table. something that could work for you, I believe.
Aug 26 2021 08:59 AM
Power Query is probably the best way to do this, but if you prefer formulas, see the attached version. The formulas will work in all versions of Excel. In Excel 365 they could be simplified.
Aug 26 2021 09:05 AM
Aug 26 2021 09:09 AM
Aug 26 2021 10:03 AM
Small trick. Let say we have table
Keep Duplicates from menu, PQ generates something like
in formula bar change [Count] > 1 on [Count] = 1
Now we have all but duplicates, not necessary to generate the same by grouping and counting.
Aug 26 2021 10:13 AM - edited Aug 26 2021 10:15 AM
@Sergei Baklan Thx. Will look at this later. Right now, I'm trying to solve problems without (too much) M-coding, although I do recognise that it is necessary to come up with the really nice solutions.
Edit: See now what you mean. not much M-coding at all
Aug 26 2021 10:36 AM
@Riny_van_Eekelen , only to change > on = in formula bar, really not a much of coding.
Sep 01 2021 10:26 AM
I appreciate the answers that I received. I had a little trial and error. I had to open the samples provided to follow the exact steps as they are not intuitive to someone new to power query and then had to study my raw data multiple times before I actually trusted the results. I did upgrade my version of excel (was MS 2016 but is now MS 365 in the cloud) so that things worked a little more like what I was being told to do. I learned something (I think) about splitting columns. I had some data that belonged to the same project but the project identifier was followed by a letter to distinguish a subset of info for that project. I thought that if I split the column, the data would be viewed as distinct in the remaining column but excel seemed to remember that the field had contained the letter. I removed the letter from the raw data prior to the query and it worked like a dream. Thanks to everyone who responded. Unfortunately for you, you will likely hear from me again as I learn new excel tricks to make my data analysis easier.