Duplicate Entries [SOLVED]

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi,


    we have a database about 30,000 line items which has 3 columns i.e., date, employee no and one column for (breakfast, lunch and dinner). Now if our employee use twice breakfast, lunch or dinner we want to know which employee have twice or single. As you know each employee is only allowed once each meal not twice. If his card is punched or manual entry. The database is maintained anyhow if he used twice also.


    Please help me easy formula or Array formula which give me a list of employees who used more than once each meal. Thanks for help.


    Khokhar

    Try this


    1. Add One Column (Co1 E) called concatinate after the meal.
    2. Paste following formula in E2 and copy till your record end
    =A2&TRIM(D2)&TRIM(C2)
    3. This will give you a unique record for each date, meal and batch (As each employee can have each meal only once)
    4. Now in Col F (Count) add following formula
    =COUNTIF(E:E,E2)
    5. This will give you the count of such unique record, which should be only = 1.
    6. Now enable either An Auto Filter or an Advanced filter to weed out the employees where the count is greater that 2.


    No Need for Pivot table here man, just keep it simple.


    If you want this to be completely automated with VBA, post back.


    Attached sheet for your reference.

    Thanks: ~Yogendra

Participate now!

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

Register Yourself Login