Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2012-03-28
Medium Priority
246 Views
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
Question by:Excellearner
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 42

Expert Comment

ID: 37780110

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

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 37780254
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

LVL 42

Accepted Solution

dlmille earned 1000 total points
ID: 37780283
@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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll