analyzing data to remove some data but retain other data - probably a very simple issue

[taylomm]

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.

[#2]

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

[#3]

@taylomm 

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.

[#4]

is power query a 365 or excel add on? I saw something about using a query to do this when I was squirreling around yesterday but have not ever used it. It sounds fabulous. How do I start?

[#5]

@taylomm 

In Excel 2010 and 2013, Power Query is an add-in that has to be activated; in Excel 2016, 2019 and 365 it is built-in and available directly from the Data tab of the ribbon.

See The Complete Guide to Power Query 

[#6]

@Riny_van_Eekelen 

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.

[#7]

@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 :)

[#8]

@Riny_van_Eekelen , only to change > on = in formula bar, really not a much of coding.

[#9]

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.

@taylomm