Identify records based on multiple conditions & single occurance of value

    Excel 2007.
    My title probably doesn't explain very well what I need to do.


    Basically it is not just to identify duplicates based on multiple criteria, but also then to return a value for all of the entries, if another criteria is met by any one of the duplicate records.


    I have a table listing all payments made iro people on a course, the columns to compare / analyze are the Client ref, the start date and the Registration fee, which may be at a reduced rate.
    There are a number of other columns re different payments such as Child Care and Travel and each payment is listed separately. Therefore the Registration fee will not always be completed, but will appear against one different start date per client and a client may have a number of entries.
    I can identify the duplicates across the client ref and start date, however I need to return "Reduced" for all incidences if the client had a reduced registration fee in order to identify all payments made based on a reduced registration fee.

    I have attached a sample workbook, the formula I am using (Column A) is returning "Reduced" for those duplicate client refs and start dates, but only if the Registration fee column is completed with the rate of 200, whereas I need it to return "Reduced" in column A against those rows highlighted in yellow as they are relevant to the course in question.
    The Client reference will not necessarily be in numerical order.


    Any help would be hugely appreciated as I really am just going round in circles.

    Re: Identify records based on multiple conditions & single occurance of value


    Edit: Scratch that - I get it now.


    Do you need to use the column structure in your example or can we move stuff around?
    Also, what about sorting the data? Is that allowed?

    Re: Identify records based on multiple conditions & single occurance of value


    Infomage, apologies for delay in replying!
    In consideration of the end users, (who agreed with the structure) and the varying degree of Excel knowledge, I would prefer that it was unchanged and didn't need to be sorted. However, at this stage I am so stuck, I would consider anything :), so anything you have would be appreciated.
    The end data will have in excess of 20,000 rows and will span the last few years. Do you think the solution could be VBA?

    Re: Identify records based on multiple conditions & single occurance of value


    Without a doubt. I'll take a look later today if I get the chance. :)

    Re: Identify records based on multiple conditions & single occurance of value


    Try this. Look at the code (<Alt>-f11 to get the VBA editor up). I have commented so hope it helps.


    The solution I have provided is a UDF (user defined function) so pay careful attention to the way the formula in the first column is constructed. It is also scaleable, no matter how many data rows you have.


    Hope it works for you. :)


    forum.ozgrid.com/index.php?attachment/72910/

    Re: Identify records based on multiple conditions &amp; single occurance of value


    Absolute genius! Huge thanks, I am quite a novice when it comes to VBA, your code is amazing in its neatness and effectiveness!!!!

    Re: Identify records based on multiple conditions &amp; single occurance of value


    The solution above relies on hard coded values to determine whether a discount was applied or not... It would be straightforward to add another parameter which is the undiscounted price, which you could then have as a value in a cell, but that may be overkill for your current requirement.


    Happy to help.

    Re: Identify records based on multiple conditions &amp; single occurance of value


    For this one, the hard coded values are grand though as a VBA learning experience, I may attempt to do as you suggest :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login