Need to assign value to one transaction per day value condition on multiple text field

[Partha Speaks]

Hello Experts,
I am having trouble in calculating a the 'final value' column in the below table. Imagine you have a text column 'sample_card' which has multiple transactions over a month. Now, a particular card is eligible for one txn per day only. Maximum discount VALUE PER TXN is set at 75. I have to determine what value a particular card is eligible for discount.
I have more than 30000 such records. Need some formula to get the column 'Final_value'.

I have tried using countif, it didn't work. I tried concatenating date and sample_card , and then countif (sample_card all colum, cample_card that row's column). It didn't work.
Kindly help.

row no	sample_card	date	txn discount passed	MAX DISCOUNT VALUE	final value
1​	0002357838937492324	2-Oct-19​	50​	75​	50​
2​	0002357838937492324	2-Oct-19​	40​	75​	0​
3​	0002357838937492324	5-Oct-19​	70​	75​	70​
4​	0001675759313894932	12-Oct-19​	80​	75​	75​
5​	0001675759313894932	12-Oct-19​	50​	75​	0​
6​	0001675759313894932	15-Oct-19​	30​	75​	0​
6​	0001675759313894932	15-Oct-19​	70​	75​	70​
6​	0001675759313894932	2-Oct-19​	40​	75​	40​

 

[GlennUK]

You have 3 row 6's ... and the second one gets a non-zero value, but the first one gets a zero .... why does the first one get the zero for that day?

 

[Partha Speaks]

Hi please ignore row number. it should 6, 7, 8.
the second row gets non-zero because if you look at the date 15th Oct., it has 2 txn and maximum discount row needs to be given the value and thus its non-zero, & the other one as zero

 

[GlennUK]

Can you sort the data on Sample Card (ascending), Date (ascending), and Txn Discount Passed (descending)?

 

[Partha Speaks]

Can you sort the data on Sample Card (ascending), Date (ascending), and Txn Discount Passed (descending)?

how do I do that using some algorithm in python or SAS?
its a large transaction file and I need to select each day's max discount for a card. please help.

 

[GlennUK]

how do I do that using some algorithm in python or SAS?
its a large transaction file and I need to select each day's max discount for a card. please help.

How do you do that? Use the Data Sort functionality of Excel.

So, can you do that to your data? If not, why not?