Trying to find duplicate visits

[david0925]

Hi all,

I am trying to identify duplicate visits in a sheet in which the duplicate column will read the Date and ID of the same row and compare them against different rows to see if both Date and ID are identical. If true, Duplicate will return as Yes. Please see the list below for what I am looking for. This was done manually and I was hoping to see if I can find a formula for it.

thanks

Date ID Duplicate
02/05/19 32 No
01/29/19 6 No
01/23/19 22 No
01/16/19 17 No
01/15/19 7 No
01/08/19 5 No
12/20/18 30 No
12/19/18 33 No
12/18/18 73 No
12/14/18 87 No
12/13/18 15 No
12/12/18 13 No
12/11/18 117 Yes
12/11/18 18 No
12/11/18 117 Yes
12/11/18 19 No
12/10/18 111 No
12/06/18 412 No
12/03/18 23 Yes
12/03/18 23 Yes

[Logit]

.
In C2 and copied down the column :

=IF(COUNTIF(A:A,B2)>1,"Yes","No")

[david0925]

.
In C2 and copied down the column :

=IF(COUNTIF(A:A,B2)>1,"Yes","No")

Hi Logit,

Thanks for your help, but right now the column returns "No" for all entries, when I am expecting 4 Yes'es as shown above.

[dflak]

For future reference. Normally people don't respond to threads without an attached workbook because of the work it takes to set the workbook up. In this case it was relatively simple. However, if you do include a workbook, it will increase the chances that someone will respond.

There are two formulas needed to make this work. One is what I call a "Composite" - since it is the combination of a date and an ID that makes a record unique, concatenate the two of them together to make the composite. I usually throw in a delimiter to make it easier to read and debug: =A2&":"&B2 the results come out looking like 43501:32. It doesn't matter that the date comes out in numerical format.

The second magic formula is: =NOT(MATCH(C2,$C$1:$C$21,0)=ROW()).

MATCH(C2,$C$1:$C$21,0) returns the row on which the value in C2 is first found. If this happens to be the current row then the expression is TRUE which means that this row is the first occurrence of the composite. For any subsequent occurrence of the composite, Match will still point to the first row on which the value was found, but the current row will be something higher, so the expression evaluates to false. So you have a true the first time the value is found and a false every time thereafter,

The NOT() just reverses the logic.

[Logit]

.
Sorry. Try this edit.

=IF(COUNTIF(A:A,B1)>1,"Yes","No")

[david0925]

For future reference. Normally people don't respond to threads without an attached workbook because of the work it takes to set the workbook up. In this case it was relatively simple. However, if you do include a workbook, it will increase the chances that someone will respond.

There are two formulas needed to make this work. One is what I call a "Composite" - since it is the combination of a date and an ID that makes a record unique, concatenate the two of them together to make the composite. I usually throw in a delimiter to make it easier to read and debug: =A2&":"&B2 the results come out looking like 43501:32. It doesn't matter that the date comes out in numerical format.

The second magic formula is: =NOT(MATCH(C2,$C$1:$C$21,0)=ROW()).

MATCH(C2,$C$1:$C$21,0) returns the row on which the value in C2 is first found. If this happens to be the current row then the expression is TRUE which means that this row is the first occurrence of the composite. For any subsequent occurrence of the composite, Match will still point to the first row on which the value was found, but the current row will be something higher, so the expression evaluates to false. So you have a true the first time the value is found and a false every time thereafter,

The NOT() just reverses the logic.

Thanks lot for your help. I tried to attach a worksheet via editing but it seems like it didn't save. I think I understand your method here. By doing this, it will not flag the first time the duplicate shows up (since when read top down it wouldn't have been a duplicate until a second identical value is found), is it possible to flag the first value as well? If not that's perfectly fine.

I guess alternatively, is it possible to use the same formula on a different column, but somehow have Excel read it backwards, and then on a 3rd column, have a return value if either first or second column return as true?

[david0925]

.
Sorry. Try this edit.

=IF(COUNTIF(A:A,B1)>1,"Yes","No")

Thanks, this still returns "No" for all values for me though.

[dflak]

That's actually easier. You still need to calculate the composite in column C but = Countifs($C$1:$C$21,C2)>1 - this evaluates to true if there are more than one of them.

Here are the canned instructions and advice on how to attach a file.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[david0925]

That's actually easier. You still need to calculate the composite in column C but = Countifs($C$1:$C$21,C2)>1 - this evaluates to true if there are more than one of them.

Here are the canned instructions and advice on how to attach a file.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

thanks a lot, this seems to have worked pretty perfectly. Also testing attachment here.

[dflak]

There you go! Welcome to the forum.