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

Occasional Visitor

# 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

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

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

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

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