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

%3CLINGO-SUB%20id%3D%22lingo-sub-2280992%22%20slang%3D%22en-US%22%3EFor%20all%20rows%20that%20are%20same%20in%20first%20column%2C%20compare%20rows%20in%20second%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280992%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20help%20writing%20a%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20my%20formula%20to%20look%20at%20the%20values%20in%20column%20A%2C%20and%20for%20all%20rows%20with%20the%20same%20value%20in%20column%20A%20then%20compare%20the%20values%20in%20column%20AC%20to%20tell%20me%20if%20they%20are%20the%20same%20or%20different.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20A%20I%20have%20employee%20ID%20numbers.%20In%20column%20AC%20I%20have%20whether%20the%20employee%20was%20at%20a%20specific%20time%20salaried%20or%20hourly.%20I%20need%20a%20formula%20which%20looks%20at%20this%20data%20and%20tells%20me%20if%20the%20employee%20was%20always%20salaried%20or%20not.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EA%20AC%26nbsp%3B%3C%2FP%3E%3CP%3E1%20Hourly%3C%2FP%3E%3CP%3E1%20Hourly%3C%2FP%3E%3CP%3E1%20Hourly%3C%2FP%3E%3CP%3E1%20Salary%3C%2FP%3E%3CP%3E1%20Salary%3C%2FP%3E%3CP%3E2%20Salary%3C%2FP%3E%3CP%3E2%20Salary%3C%2FP%3E%3CP%3E2%20Salary%3C%2FP%3E%3CP%3E2%20Salary%3C%2FP%3E%3CP%3E3%20Hourly%3C%2FP%3E%3CP%3E3%20Salary%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%20displayed%20data%2C%20Employee%20ID%201%20and%203%20were%20not%20always%20salary%2C%20but%20Employee%20ID%202%20would%20be.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20that%20can%20look%20at%20that%20and%20tell%20me%20that%20for%20thousands%20of%20employee%20data.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2280992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281298%22%20slang%3D%22en-US%22%3ERe%3A%20For%20all%20rows%20that%20are%20same%20in%20first%20column%2C%20compare%20rows%20in%20second%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033211%22%20target%3D%22_blank%22%3E%40KimberlyHoldiman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20ids%2C%20%20%20%20%20A2%3AA12%2C%0A%20type%2C%20%20%20%20B2%3AB12%2C%0A%20id%2C%20%20%20%20%20%20UNIQUE(ids)%2C%0A%20types%2C%20%20%20IF(COUNTIFS(ids%2Cid%2Ctype%2C%22Salary%22)%3DCOUNTIFS(ids%2Cid)%2C%20%22Salary%22%2C%20%22Mix%22)%2C%0AIF(%7B1%2C0%7D%2C%20id%2C%20types))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281171%22%20slang%3D%22en-US%22%3ERe%3A%20For%20all%20rows%20that%20are%20same%20in%20first%20column%2C%20compare%20rows%20in%20second%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033211%22%20target%3D%22_blank%22%3E%40KimberlyHoldiman%3C%2FA%3E%26nbsp%3BNow%2C%20if%20this%20is%20just%20a%20one-time%20exercise%2C%20you%20could%20create%20a%20pivot%20table%20counting%20the%20number%20of%20occurrences%20for%20Hourly%20and%20Salary%20for%20each%20employee.%20Then%2C%20a%20%22quick-and-dirty%22%20COUNT%20function%20can%20determine%20which%20employees%20have%20entries%20in%20both%20or%20only%20one%20of%20the%20categories.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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