Solved

populating value in last record based on multiple conditions

Posted on 2012-03-28
15
247 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 41

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 41

Expert Comment

by:dlmille
ID: 37779039
In meetings but will revert shortly
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 41

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 41

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 41

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 41

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 41

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 41

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

803 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