Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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

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

[D2]=IF(AND(COUNTIF($A$2:$

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

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,

Put this in D2 and copy down:

[D2]=IF(AND(COUNTIF($A2:$A

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

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

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

Thanks,

Dave

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,

[D2]=IF(AND(COUNTIF($A2:$A

And copy down

Please advise.

Dave

Dave

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

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(INDE

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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