Solved

populating value in last record based on multiple conditions

Posted on 2012-03-28
15
252 Views
Last Modified: 2012-03-28
Dear experts
Need a excel based formula which check for 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.

Kindly help.

thank you
value-in-the-last-record.xlsx
0
Comment
Question by:Excellearner
  • 8
  • 7
15 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37778668
This formula you can paste in D2 and copy down.  Note, it accomodates through row 100, so expand that if you have more rows:

[D2]=IF(AND(COUNTIF($A$2:$A$100,$A2)=1,$B2<>1),0.1,"")

Basically, it counts the instances of each Row's column A with the entire Column A.  If the count equals 1, then its unique.  At the same time, it compares column B on that row with 1, an dif not equalt to 1, .1 (10%) is returned in column D, otherwise the null string (nothing) is returned.

See attached.

Dave
value-in-the-last-record.xlsx
0
 

Author Comment

by:Excellearner
ID: 37778974
Dlmille,

Thank you for the solution,

I missed to mention one important condition, which was the last record and which is greater than 1, will be populated with 10%. Also if there is only one record for a variable and its value in columnb =1, then 10% will be populated.

the abovemeans that every record will certainly get one 10%.

Now I did not mention in theoriginal question, but i have mentioned in the required solution.

Since this is enhanced requirement do you want me to raise another question.

Kindly help as this request is urgent.

thank you,
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37779039
In meetings but will revert shortly
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 42

Expert Comment

by:dlmille
ID: 37779581
Ok, just a slight change in formula, making $A2 relative reference, so as the formula is copied down, the last occurrence of a variable will be = 1 in the COUNTIF function when you get to that row.

Put this in D2 and copy down:

[D2]=IF(AND(COUNTIF($A2:$A$100,$A2)=1,$B2<>1),0.1,"")

If I'm understanding you correctly, 10% goes into the LAST occurrence of a Variable in the list (from top/down) if and only if that LAST occurrence has a number not equal to 1 in column B.

See attached.

Dave
value-in-the-last-record-r1.xlsx
0
 

Author Comment

by:Excellearner
ID: 37780024
dlmille,

Thank you for the formulae,

Basically every unique variable should get only one 10%, but it shall to the last record in the column and whichhas got a value <>1 in the column b.

Kindly help.

Thank you
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37780031
Ok - that's the way it works.  What's not working?  The only items that aren't getting the 10% are those that have 1.

Please share what you desire as the output, as we're not tracking very well, so we can be more efficient.

Thanks,

Dave
0
 

Author Comment

by:Excellearner
ID: 37780032
dlmille,

One another point.

In addition to the above, if the value in column appears only once, even then it will get a 10%, as I mentioned earlier, every unique variable should get one record of 10%.

thank you,
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37780037
Given your clarification, your formula should be (to deal with the EVEN IF):

[D2]=IF(AND(COUNTIF($A2:$A$100,$A2)=1,$B2<>1),0.1,IF(COUNTIF($A$2:$A$100,$A2)=1,0.1,""))

And copy down

Please advise.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37780052
Maybe we should have agreed to post as a new question.  The more you tell me the more the criteria keeps changing, lol.  (or at least the more you reveal with each comment, over and above the original comments).

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37780091
Ok - I've taken all your comments and your original output into account.  My interpretation of the criteria is this.

There are 2 criteria:

1.  The value in Column A is unique, thus 10% is the result
2.  If the value in Column A is NOT unique, then the last occurrence that has a B column <> 1 should get 10%

See attached, which matches your original output and advise.

Cheers,

Dave
value-in-the-last-record-r2.xlsx
0
 

Author Comment

by:Excellearner
ID: 37780106
dlmille,

Thank you for the solution,

I have now raised another question.

Sorry for this confusion, this is a solid example for poorly put question.

Thankyou
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37780107
I added one more criteria.  

3.  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.

This was a tough one on several accounts.  I hope it works for you ;)

Dave
value-in-the-last-record-r3.xlsx
0
 

Author Closing Comment

by:Excellearner
ID: 37780108
Thanks much, i have raised another question kindly provide support on the new question.
0
 

Author Comment

by:Excellearner
ID: 37780120
Also, if you explain the logic of the formulae either in this quesiton or in the new one. This will help me understand.

Thank you.
0
 

Author Comment

by:Excellearner
ID: 37780123
dlmille,

Your thoughts on your last solution was superb. I did not imagine such a situation.

Thank you very much once again.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 use a scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question