Assistance with duplication values in excel-CodePudding

I've got an excel sheet here where I'm trying to see if I need to apply a user license, device license, or none, and this is based on whether or not there are duplicates within a column. For example, if there's a unique entry in column A, the corresponding entry in column B should read "device". What complicates things though would be the "user licenses" where a "User license" would function for 5 accounts, and everyone after the 5th license would also be counted as a device. Any ideas for a formula that could help me get through 22k lines of this mess?

A   User
A   N/A
A   N/A
B   User
B   N/A
B   N/A
B   N/A
B   N/A
B   Device
B   Device
B   Device
B   Device
C   Device

CodePudding user response：

Put this in B1 and copy down:

=IF(OR(COUNTIF(A:A,A1)=1,COUNTIF($A$1:A1,A1)>5),"Device",IF(COUNTIF($A$1:A1,A1)=1,"User","N/A"))