Excel SUM check on duplicates

Question

I am working on a data sheet where I have a list containing team members and their allocated tasks each month in percent.

I want to do a check where I see if there are any duplicates and if this is true I want to check if work allocation is above 1.

If this happens I would like to somehow indicate those team members are over-allocated. I thought this could be done with color marking on the name or a cell showing names that are over-allocated.

I attached this example with 11 names where David and Martin are overallocated.

Can I do this with basic Excel statement or VBA code?

Thanks for your help.

//David

Sounds like a job for a pivottable and filter out count <2. Assume this as an allocation of over 1 in a month? — QHarr, Feb 07 '18 at 12:55
Yes. a duplicate is a name appearing more than once in column A. — TheDave, Feb 07 '18 at 12:55

Glitch_Doctor · Answer 1 · 2018-02-09T12:16:08.927

2

In your example David is over allocated by 0.1 for January...

You could have 2 formulas to get your desired result, this is an example of just 1 month, drag the second formula across for the remainder:

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(A:A,$A2)>1,ROW(A:A)),ROW(1:1))),"") - First column (this is an array formula, while still in the formula bar hit Ctrl+Shift+Enter)

This will return the smallest row number for index, where the count of the result in column A is more than 1...

=IF($P2<>"",SUMIF($A:$A,$P2,B:B)-1,"") - Assuming P is the column with the first formula.

Edit: For Apple Mac an array formula is entered like so:

(taken from this Stack Overflow answer)

Select the range, press CONTROL+U and then press ⌘+RETURN.

edited Feb 09 '18 at 12:16

answered Feb 07 '18 at 13:13

Glitch_Doctor

2,994
3
16
30

Hmm. I could not get it to work on my Mac with the formular. Stupid mac :D – TheDave Feb 08 '18 at 17:48
@TheDave For future reference, array formulas can be enetered for Mac following [this method](https://stackoverflow.com/a/26605282/6387479) – Glitch_Doctor Feb 09 '18 at 12:14

QHarr · Accepted Answer · 2018-02-07T13:39:52.557

Pivottable method (Note I used some dummy actual values):

Set your data up as a table and add an end column called NameCount. Assuming your first column you put a header name in cell A1 then you can put the following formula in the new column top cell e.g. 02 and it will autofill down.

 =COUNTIF([Name],[@Name])

Dummy data:

Create a pivot off the data and arrange like so:

Filter out anything <= 1 from the NameCount.

Apply conditional formatting to the column ranges in your pivottable containing the month data:

Formula:

=AND(ISNUMBER(C14),C14>1)

Make sure $ is not present as you want the formula to be applied to all cells in range.

An example range (I have kept this short) for the formula to apply to:

Excel SUM check on duplicates

2 Answers2