Counting first instance of recurring value against unique text in another column

[sarithakola]

Hi, here is a sample data set the result I want from the formula is in the last column Overallocated Employee;

<tbody>
[TD="class: xl63, align: right"][/TD]

</tbody>

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Employee[/TD]
[TD]COUNTIFS(B:B,B2)[/TD]
[TD]Overallocated Employee(more than 1 ID assigned.)[/TD]
[/TR]
[TR]
[TD]XMP001[/TD]
[TD]Jim[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]XMP002[/TD]
[TD]Mary[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]XMP003[/TD]
[TD]Steve[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]XMP004[/TD]
[TD]Bob[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP005[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP006[/TD]
[TD]Helen[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP007[/TD]
[TD]Jim[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP008[/TD]
[TD]Mary[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP009[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP010[/TD]
[TD]Dan[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP011[/TD]
[TD]Jacob[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP012[/TD]
[TD]Steve[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP013[/TD]
[TD]Amanda[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]XMP014[/TD]
[TD]Amanda[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XMP015[/TD]
[TD]Mary[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I want the value in Overallocated Employee column to be turned to 1 any overallocated employee (i.e. employees with more than 1 IDs assigned to them), for the first occurence of overallocation. I basically want to be able to get unique count of overallocation employees. I am using COUNTIFS in third column to see how many allocation each employee has, but it repeats.
Please help with the formula to get the last column above.

Thanks,
SK

 

[Dreadknight Nasus]

With the upper left corner of the table in A1, this is the formula I had in D2 and it seemed to work for me:

=IF(AND(C2>1,ROW()=MATCH(B2,B:B,0)),1,0)

It gives you errors with the blanks, though. If you want to get rid of those, you can just wrap the formula in a IFERROR( ,""). Let me know if that works for you.

 

[Aladin Akyurek]

What are the desired values - ones in the fourth column?

 

[sarithakola]

What are the desired values - ones in the fourth column?

Yes. Thats correct. I manually created 4th column to show the output that I want to get.
Thanks

 

[Aladin Akyurek]

Yes. Thats correct. I manually created 4th column to show the output that I want to get.
Thanks

C2, control+shift+enter, not just enter, and copy down:

=IF($B2="",0,IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),0,
  IF(SUM(IF(FREQUENCY(IF($A$2:$A$16<>"",IF($B$2:$B$16=$B2,MATCH($A$2:$A$16,$A$2:$A$16,0))),
  ROW($A$2:$A$16)-ROW($A$2)+1),1))>1,1,0)))

 

[sarithakola]

C2, control+shift+enter, not just enter, and copy down:

=IF($B2="",0,IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),0,
  IF(SUM(IF(FREQUENCY(IF($A$2:$A$16<>"",IF($B$2:$B$16=$B2,MATCH($A$2:$A$16,$A$2:$A$16,0))),
  ROW($A$2:$A$16)-ROW($A$2)+1),1))>1,1,0)))

Thanks Aladin. This works like magic

 

[sarithakola]

Hi Dreadknight, this most definitely works, but like you said the blanks are a problem Thanks for the response, please see Aladin's code below that takes care of blanks as well.

SK

 

[Dreadknight Nasus]

Hi Dreadknight, this most definitely works, but like you said the blanks are a problem Thanks for the response, please see Aladin's code below that takes care of blanks as well.

SK

I gave you instructions on how to get rid of the errors.

 

[Aladin Akyurek]

Thanks Aladin. This works like magic

You are welcome.