• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help!!!

D

dckrtfm

New Member
Hi,


I have a table of data that I want to find the last date a company was rewarded unter a loyalty scheme. The problem I have is that the trigger point for the reward is 10 items so I need to be able to count the number of times the company appears and then if they have been rewarded in the last 12 months skip the result.


Any help would ge greatly appreciated.
 
Hi,


Apologies for the lack of information - posted in a hurry and forgot that you cannot attached a file!


What I have is a table of dates spanning columns A to AA. In this table I have information relating to individual transactions recorded against a particular outlet i.e. company XXX, address details, product sold and who to etc. I then have a series of columns within this table that look at the rewards paid to the particular companies. These rewards are done on a per 10 basis. So when a company has made 10 applications for product and completed the purchase of the product then they are due for a reward.


I have tried to get the information I am after in a pivot table but struggle to get the dates the rewards were paid as it is against 10 individual transactions. What I have done is composed a separate table along side the main table with a few vlookup and if statements to get the name of the companies, the number of applications, the number of completed sales. What I am finding difficult is the final part where I identify the dates the last reward was paid and the date the last purchase was completed. I have sum to get the details in the second table I cannot get the information to match as I cannot work out how to reference the first table to the second table.
 
Is there a separate column for when rewards were paid and when purchases were made? If you have 1 column with dates of purchases, and you need to know when the last "10th" transaction was made, something like:

=SMALL(A:A,10*INT(COUNT(A:A)/10))

will give you that date.

If we need to break that down by company, could use an IF array (use Ctrl+Shift+Enter to confirm) to limit the data, like:

=SMALL(IF(B1:B100="XXX Company",A1:A100),10*INT(COUNT(IF(B1:B100="XXX Company",A1:A100))/10))
 
Hi Luke,


Thanks for the reply. Yes there is a seperate columns for when purchases are made and reards paid. Purchases are Column P and Rewards are Column Z. I have tried the IF array above changeing the B and A to P and Z respectively. Unfortunately I get a #N/A result.
 
You must log in or register to reply here.
Back
Top