How to find TRUE DUPLICATES?

[anmac1789]

Hello, I am trying to find secondary duplicates together with Matching a specific path. For example, I am trying to match - size, date created, date modified, date accessed and highlight these rows across the entire table while not highlighting other rows for each these 4 properties are not matching. For example, suppose that there is an error made somehow and 2 files have the same 3 dates and times, size and name but when viewing the photos, they are completely different picture, if this is the case, then is there a 5th property that I can check for to make sure two files are TRULY distinct ? Btw, how can I use textjoin with conditional formatting in "use a formula to determine which cells to format" under the new formatting rule?

FYI - I used voidtool's software called Everything to populate a list with filenames, sizes, 3 dates and times -- date created, date modified, date accessed (4 properties so far). I choose not to match the name because there can be duplicates (such as file1.jpg, file1 (1).jpg)

 

[Skovgaard]

Hi,

There might be an easier solution, but below should work.
I've created two helper-columns (Concat in column A and G), and then just did a countif, see below (notice you should probably change from semicolon to comma) .
If the count is above 0, there is a duplicate.
You can also implement this in conditional format, if you want the colors, but in this way, you can simply use the filter function.

/Skovgaard

 

[anmac1789]

Hi,

There might be an easier solution, but below should work.
I've created two helper-columns (Concat in column A and G), and then just did a countif, see below (notice you should probably change from semicolon to comma) .
If the count is above 0, there is a duplicate.
You can also implement this in conditional format, if you want the colors, but in this way, you can simply use the filter function.

View attachment 75662

/Skovgaard

What if the problem becomes that for example 2 filenames (XnKTgnu7Ny1LvkvaulNrqOHVCsk=PHOTO-2019-11-08-23-08-12, tkTMpj3cPFl0H1ofBoGlRzWUiN4=PHOTO-2019-11-08-23-08-10) have the same size of 8,238 bytes. Both of these filenames are located in J:\photos app and have ‎Friday, ‎November ‎08, ‎2019, ‏‎10:08:10 PM as date created, date modified and date accessed. The same two duplicate files are located in other folders with the date created, date modified and date accessed set as ‎Friday, ‎November ‎08, ‎2019, ‏‎11:08:10 PM.

What is the reasoning for creating 2 concat columns?

Please seee above image for illustrative purposes of the problem

 

[Skovgaard]

Then you exclude the path in your concat formula. It should only contain the exact values, that you want to compare.
I thought you were comparing two lists, if all transactions are in one list, you only need one concat.

/Skovgaard

 

[anmac1789]

Then you exclude the path in your concat formula. It should only contain the exact values, that you want to compare.
I thought you were comparing two lists, if all transactions are in one list, you only need one concat.

/Skovgaard

Oh no, there is only one list to compare but even when running a countif function, it basically re-created another concat list anyway...therefore still no way to isolate and show exact duplicates...please see the below picture

 

[Skovgaard]

Oh no, there is only one list to compare but even when running a countif function, it basically re-created another concat list anyway...therefore still no way to isolate and show exact duplicates...please see the below picture
View attachment 75675

Put you concat list in e.g. column G, and include the filename also like this: =A2&C2&d2&E2&F2.
Then put put you countif in column H like this: =COUNTIF($G$2:$G9,G2)

/Skovgaard

 

[anmac1789]

Put you concat list in e.g. column G, and include the filename also like this: =A2&C2&d2&E2&F2.
Then put put you countif in column H like this: =COUNTIF($G$2:$G9,G2)

/Skovgaard

What if I didn't want to take into account the name because duplicate files could also be file1.jpg or file1 (1).jpg?

 

[anmac1789]

I guess a workaround is that for each countif value, assign a colour to it matching the "block" of concated cells and run a condition that in that block if all values are the same (all count if values are one number) then the block of cobtacted values are true unique duplicates. If the countif values are different or appear as "kinks" of different colour then that block of same size dupes are not true duplicates. I know that was a lot but does the reasoning make sense ? So i guess the question now becomes to find true duplicates, how to set up a dependent conditions ?

 

[Skovgaard]

In exactly that situation, you could put in the first 5 characters in the concat (file1), but I guess that wont suit for the rest of the filenames.

How would you define a TRUE Duplicate from the given data then?

/Skovgaard

 

[anmac1789]

In exactly that situation, you could put in the first 5 characters in the concat (file1), but I guess that wont suit for the rest of the filenames.

How would you define a TRUE Duplicate from the given data then?

/Skovgaard

Ive explained a little bit in detail in post 8 althought in a very hack and slash manner lol sorry