# populating value in last record based on multiple conditions for every unique value

Dear experts
Need a formulae which checks for every unique value in column A (variable) and at the same check for value not equal to 1 under column B, and then post 10% in column D. IF there are multiple records for a value in column A with column B <>1, then the last record should get the posting.
In addition, if there is only one occurence of a value under column A, then irrespctive of the value under column, 10% will be populated under column D.

the abovemeans that every record will certainly get one 10%.
value-in-the-last-record.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

If the value in Column A is NOT unique, then if all occurrences are 1, then the last occurrence will get 10%

See attached.

Here's the formula:
=IF(\$B2=IFERROR(INDEX(INDEX(\$A\$2:\$B\$14,,2),LARGE(IF((INDEX(\$A\$2:\$B\$14,,2)<>1)*(INDEX(\$A\$2:\$B\$14,,1)=\$A2),ROW(OFFSET(\$A\$1,,,ROWS(\$A\$2:\$A\$14))),""),1),1),1),IF(\$B2=1,IF(COUNTIF(\$A2:\$A\$14,\$A2)=1,0.1,""),0.1),"")

It is array entered - hit F2 on the formula, then CTRL-SHIFT-ENTER

See attached.

Dave
value-in-the-last-record-r3.xlsx
EngineerCommented:
You can also try a non-array formula

=IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,1),IF(COUNTIF(A:A,A2)=COUNTIF(\$A\$1:A2,A2),10%,""),IF(B2<>1,IF(COUNTIFS(A:A,A2,B:B,"<>1")=COUNTIFS(\$A\$1:A2,A2,\$B\$1:B2,"<>1"),10%,""),""))
Commented:
@ssaqibh - good one.  I originally wrote for Excel 2003 compatibility, then threw in the IFERROR when I noticed the sheet posted was Excel 2007+ to shorten the formula.

Your formula can be shorted a bit with two less ifs and one 10%/"" result respectively:

=IF((COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,1))*(COUNTIF(A:A,A2)=COUNTIF(\$A\$1:A2,A2)), 10%,IF((B2<>1)*(COUNTIFS(A:A,A2,B:B,"<>1")=COUNTIFS(\$A\$1:A2,A2,\$B\$1:B2,"<>1")),10%,""))

Dave

Experts Exchange Solution brought to you by