Apr 21 2021 05:13 PM
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.
Apr 21 2021 10:33 PM
@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.
Apr 22 2021 01:55 AM
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))