• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
0
Excellearner
Asked:
Excellearner
  • 2
2 Solutions
 
dlmilleCommented:
I think this is it.  Please advise:

I added one more criteria.  

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
0
 
Saqib Husain, SyedEngineerCommented:
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%,""),""))
0
 
dlmilleCommented:
@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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now