Filter out repeating values and apply formula on the second or 4th value only

Dear Experts

I need help on a long list of transactions where I want to filter out those customers who have traded more than one time and then apply a formula only to the second or fourth transaction to calculate the discount value. Is it possible through Forumla or Macro will be better? Example data below

Date Customer Number Revenue
2-Nov-18 5005542746 1,147
2-Nov-18 5005542746 640
26-Oct-18 5005543078 3,967
24-Oct-18 5005543080 270
25-Oct-18 5005543080 102
1-Nov-18 5005543080 270
30-Oct-18 5005543080 11,161

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

How does excel know WHICH one to choose... the 2nd or the 4th???

You did not show what your expected results should look like (see point 2 -in red - in my previous post). I had hoped that you would read it if it was in an eye-catching colour. But you didn't. So... I guessed:

=IF(COUNTIF($B$5:B5,B5)=2,0.85*C5,IF(COUNTIF($B$5:B5,B5)=4,0.8*C5,C5))

copied down.

Then use either this:

=IF(COUNTIF($B$5:B5,B5)=4,0.8*C5,IF(AND(COUNTIF(B:B,B5)<=2,COUNTIF($B$5:B5,B5)=2),0.85*C5,""))

or this:

=IF(COUNTIF($B$5:B5,B5)=4,0.8*C5,IF(AND(COUNTIF(B:B,B5)<=2,COUNTIF($B$5:B5,B5)=2),0.85*C5,C5))

depending on whether or not you want to see the undiscounted value

Is this "instead of" or "as well as" the previous conditions?

Duhh! It has to be instead of...

=IF(AND(COUNTIF(B:B,B5)>=4,OR(COUNTIF($B$5:B5,B5)=3,COUNTIF($B$5:B5,B5)=4)),0.8*C5,IF(COUNTIF(B:B,B5)=2,0.85*C5,""))

or

=IF(AND(COUNTIF(B:B,B5)>=4,OR(COUNTIF($B$5:B5,B5)=3,COUNTIF($B$5:B5,B5)=4)),0.8*C5,IF(COUNTIF(B:B,B5)=2,0.85*C5,C5))

depending on whether or not you want to see the unmodified values.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.