• 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.

Identify Duplicate entries

A

Abhijeet

Active Member
Hi

I want to know Duplicate entries If Date is Fall in Same week then that entries i want to mark as duplicate. In attach file i show expected result in Colors. Normally i use concatenate & If Countif formula to identify duplicate entries .Please tell me if the dates are falls in Same week then how do identify Duplicate entries
 

Attachments

  • Duplicate values look in each week .xlsx
    10.1 KB · Views: 2
Hi Abhijeet ,

1. What are the 2 colors for ? What does the amber color signify , and what does the red color signify ?

2. Why is row #15 colored amber ?

3. How are duplicate entries to be detected i.e. what are the conditions under which we can identify that any two entries are duplicates ?

Narayan
 
Hi Narayan Sir

Red Colored is for Same Dates Duplicate Entries

amber colored is for Dates are fall in Same Week Duplicate entries

I want identify duplicate entries if dates are fall in same week please tell me
 

Attachments

  • Duplicate values look in each week .xlsx
    13 KB · Views: 0
Hi Narayan Sir

In Row 8 Value1 is 12 that is not duplicate with Same Assignment & Element

I use this formula =IF(COUNTIF($L$2:$L$15,L2)>1,"Duplicate","No Dups")
but in this formula same date then gives Duplicate result but i want if date is fall in same week then that entry i want duplicate from this formula. only date criteria is different rest all of the same

In row 14 (10-May-2015 ) is date but that is fall in week 18 thats why this is not duplicate

Week i am considering Monday to Sunday
 
Hi Abhijeet ,

Sorry , still not clear.

I am uploading your file ; I have added a column L , where I have marked a few duplicates ; please fill in all the rest and re-upload the file.

Narayan
 

Attachments

  • Duplicate values look in each week .xlsx
    10.5 KB · Views: 1
Narayan Sir

Your attach file no formula mention

Here u can refer this attach file here i upload calender also in this file

From this formula =IF(COUNTIF($L$2:$L$15,L2)>1,"Duplicate","No Dups")
dates criteria i want week wise rest as it is

For week wise dates means Row 6 & 15 is fall in week 19 but Row 14 date is 10-may-15 is fall in week 18 so this way i want duplicate

Monday To Sunday consider as 1 week any day is fall between the week & rest criteria is same then that is duplicate
 

Attachments

  • Duplicate values look in each week .xlsx
    19.2 KB · Views: 1
Hi Abhijeet ,

If you want help from a forum , please specify your requirement , instead of discussing your formula. Even if I have to scrutinise your formula , I need to know what your requirement is , that is the first step.

Can you first answer my earlier posted question ?

Narayan
 
I want identify duplicate entries with the criteria
1st Criteria is Assignment
2nd Criteria is Element
3rd criteria is All field Values in this ignore dates

For dates criteria is Fall in Same week then that is Duplicate
I also mention in Row 6 & 15 date is fall in week 19 but Row 14 date is 10-may-15 is fall in week 18 so this way i want duplicate
 
Hi Abhijeet ,

I am uploading your workbook ; in column L I would request you to fill in the row numbers of the duplicates.

Narayan
 

Attachments

  • Duplicate values look in each week .xlsx
    10.1 KB · Views: 2
I have mention which values are dupliacte in Column L
 

Attachments

  • Duplicate values look in each week (1).xlsx
    10.2 KB · Views: 1
Narayan Sir

Thanks for this

Can you plaese tell me dates not fix in column that why u use =IF(C2>24,WEEKNUM(C2,2),C2) formula

If we can use each sheet for Basic, Enh, OT data then Dates are in fix column
Basic dates are always in Value2 field
Enh Dates are always in Value7 field
OT Dates are always in Value9 field
 
Can u pls look this file i have highlighted in Red color values this are not same but in this formula show the same value =IF(C2>24,WEEKNUM(C2,2),C2)

that's why show as duplicate my request is if we can split the data in sheet wise then use the formula that will help please help in this issue
 

Attachments

  • Duplicate values look in each week (1) (1).xlsx
    11.9 KB · Views: 1
yes
Also i tried to split Basic,Enh,OT & then use only where is Date that column use weeknum formula that is Done only i want to ask if i change the year in date then also show same weeknum is their any way to avoid this
 
Yes that is correct i just change the value & change the result or not but result show Duplicate result i told u you are using this formula =IF(C2>24,WEEKNUM(C2,2),C2) but my dates are not in each column Dates are fix as per element wise If Basic then Value2 is date
Enh then Date is in Value7 & For OT Dates in Value9
 
Hi Abhijeet ,

Why should you change the data just like that ?

I have time and again , in the past , said that the sample file should contain realistic data which is representative of your real-life working data ; uploading a sample file which is not representative is a waste of everyone's time.

I am sorry , but I hope someone else will help out.

Narayan
 
If data is 100000 row how should i upload that data & Data is change every month how can i upload real data

No worries i solve this problem 99% only Not give result If Day & Month Same year change then weeknum show the same that is challenge
 
Hi Abhijeet ,

If you cannot upload your workbook , it is your responsibility to describe your problem in detail. Leaving it to others to ask questions , answering them in an incomplete manner so that questions are asked over multiple posts , this is all a waste of time. If you had mentioned in your initial post that the dates can be in different years , then it could have been taken care of.

7 hours passed between the time you posted your problem , and the time the first solution was posted ; given the nature of your problem , if it had been fully described in the original post , these 7 hours could have been reduced to 1.

Narayan
 
Narayan Sir

I change for this formula its work =IF(C2>24,WEEKNUM(C2,2),C2)

Changed to =IF(C2>41640,WEEKNUM(C2,2),C2)

I do not know why u are using C2>24

01-01-2014 date Value is 41640 so i use this

After change this Its work in ur formula
 
Hi Abhijeet ,

Since I have not seen your data , I am in no position to say anything.

If it works , it is good.

If it does not , please upload your data where it does not work.

Narayan
 
Please tell me If Date is 10-May-2015 then less than 3 days means 7-may-2015
More than 3 days means 13-May-2015 how these dates are find then mark as Duplicate

Please tell me formula for this
 
You must log in or register to reply here.
Back
Top