Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

populating value in last record based on multiple conditions

Posted on 2012-03-28
15
Medium Priority
?
276 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
Industry Leaders: 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!

 
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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

916 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