Forum Discussion
For all rows that are same in first column, compare rows in second column
I am looking for help writing a formula.
I need my formula to look at the values in column A, and for all rows with the same value in column A then compare the values in column AC to tell me if they are the same or different.
In column A I have employee ID numbers. In column AC I have whether the employee was at a specific time salaried or hourly. I need a formula which looks at this data and tells me if the employee was always salaried or not.
For example:
A AC
1 Hourly
1 Hourly
1 Hourly
1 Salary
1 Salary
2 Salary
2 Salary
2 Salary
2 Salary
3 Hourly
3 Salary
In the above displayed data, Employee ID 1 and 3 were not always salary, but Employee ID 2 would be.
I need a formula that can look at that and tell me that for thousands of employee data.
2 Replies
As variant
=LET( ids, A2:A12, type, B2:B12, id, UNIQUE(ids), types, IF(COUNTIFS(ids,id,type,"Salary")=COUNTIFS(ids,id), "Salary", "Mix"), IF({1,0}, id, types))KimberlyHoldiman Now, if this is just a one-time exercise, you could create a pivot table counting the number of occurrences for Hourly and Salary for each employee. Then, a "quick-and-dirty" COUNT function can determine which employees have entries in both or only one of the categories.
An example is attached.
