populating value in last record based on multiple conditions

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
ExcellearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

dlmilleCommented:
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
ExcellearnerAuthor Commented:
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,
dlmilleCommented:
In meetings but will revert shortly
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

dlmilleCommented:
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
ExcellearnerAuthor Commented:
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
dlmilleCommented:
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
ExcellearnerAuthor Commented:
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,
dlmilleCommented:
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
dlmilleCommented:
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
dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExcellearnerAuthor Commented:
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
dlmilleCommented:
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
ExcellearnerAuthor Commented:
Thanks much, i have raised another question kindly provide support on the new question.
ExcellearnerAuthor Commented:
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.
ExcellearnerAuthor Commented:
dlmille,

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

Thank you very much once again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.