Good afternoon,
I'm having trouble replicating the following Excel formula my workflow:
=IF(COUNTIFS($A$2:A2,A2,$G$2:G2,G2)>1,0,1)
Column A is an employee ID and column G is a team ID. Since there are duplicate Employee IDs I use the formula to count the first instance that an Employee ID pops up given that the Emp ID and Team ID are the same. I am feeding this data to a Pivot Table. The formula allows me to set the Pivot Table to count for the first instance of the ID while keeping the duplicate IDs when drilling down into the data. I'm keeping the duplicate ID's because the rows show different sub categories that an employee may be supporting within one team.
Example:
EMP ID | Team ID | UniqueValCount (IF(COUNTIFS) Formula) |
251a | aa | 1 |
251a | aa | 0 |
251a | aa | 0 |
251a | bb | 1 |
252a | cc | 1 |
253a | cc | 1 |
Using the Summarize Tool doesn't allow me to assign 0's to the duplicate values after the first instance.
Any help is much appreciated!
Solved! Go to Solution.
Hi @juannc11 ,
This sounds like a good use case for the Multi-Row Formula tool. Give this a try and let me know if it works for your use case!
@juannc11
If you find my workflow useful, appreciate you would mark it as accept as well.
@juannc11
Thank you so much😁