Solved

populating value in last record based on multiple conditions

Posted on 2012-03-28
15
231 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now