Formula to look at multiple cells with the same data, but only return a value once

**jonas245** · 01-09-2013, 01:12 AM

Hi,
I am working on a roster sheet that I am having trouble working out a formula for. It should be so simple, but yet I can't seem to get it to work.

For Example: If I enter an employee number into cell A1 an in cell B1 is the allowance for uniform, I need a formula that will lookup all of column A, and if this is the first instance of this employee number cell B1 should =$2.05. But the formula needs to be able to recognise that if the employee number occurs more than once, I only want it to pay the $2.05 once and not on every occurance of the employee number

Any help would be appreciated.

Cheers

**dilipandey** · 01-09-2013, 01:25 AM

Hi Jonas245

Try looking at vlookup function, in case of any issue upload a small sample workbook. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**dredwolf** · 01-09-2013, 01:36 AM

If the Employee Number gets entered at A1, and they list of employees requesting uniforms starts at A2; and B1 is the allowance for uniforms, try this in C1:
=IF(COUNTIF($A:$A,$A$1)>1,"Already Used Allowance","Gets "&$B$1)

This Assumes the employee numbe is not already in the list, If (for whatever reason) it is, but is still eligible for the allowance if they only appear once:
=IF(COUNTIF($A:$A,$A$1)>2,"Already Used Allowance","Gets "&$B$1)

Hope this helps

Formula to look at multiple cells with the same data, but only return a value once

LinkBack

Thread Tools

Rate This Thread

Display

Formula to look at multiple cells with the same data, but only return a value once

Re: Formula to look at multiple cells with the same data, but only return a value once

Re: Formula to look at multiple cells with the same data, but only return a value once

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions