For all rows that are same in first column, compare rows in second column

Copper Contributor

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

@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.

@KimberlyHoldiman 

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))