Find Duplicates in Groups of Rows

[morerockin]

Hello All,

First and foremost, my apologies if I am asking a question that has been asked once before. I have searched for this in the forum and cannot find an answer.

My Question:

I am trying to search for duplicate values within row groupings. I have hundreds of rows that need to be searched for duplicates, and am requiring that that we are ONLY searching a grouping of rows for dups.

See attached. Let me know if clarification is needed!

Attachment 370251

Thank you!

[mcmahobt]

Unfortunately, your attachment did not seem to upload correctly. Assuming your data starts in cell A1 and populates down, through column A, try this formula in B1:

Please Login or Register  to view this content.

And drag down. Note that you will have to adjust the range to fit your data accordingly. This will tell you which cells contain duplicate data.

Hope this helps!

[morerockin]

Sorry! I have reuploaded the attachment and this time I've included a pic that helps outline what i'm looking for!

Cucumber should NOT show up as a duplicate as it is in a separate row grouping. Turkey and Tomato should come up as duplicates!

Is there a way for it to just highlight the duplicate instead of replacing the value? That is what i am going for.

See pic

2015-01-13 15_54_23-Microsoft Excel - report (1).xlsx.png

[morerockin]

$5 American US dollars to someone who can bring 100% resolution!

[FDibbins]

morerockin, we have a commercial services forum where you can pay to get help

Also, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[mcmahobt]

Place this in C2 and drag down. It will show you where the duplicates appear in column B. Note too that you will have to change the formula setting to automatic calculation.

Please Login or Register  to view this content.

[samba_ravi]

Pls attach sample file with desired result (How your output will be)

[newdoverman]

This will identify duplicates in each fridge:

Please Login or Register  to view this content.

This is similar to msg #6.......Take you choice.

Here is another way:

Please Login or Register  to view this content.

[morerockin]

Hey guys,

I want to confirm that the above works. I have one more caveat. I have found duplicates that have been marked for deletion by my transaction team.

See attached Book2.xlsx

So, I am trying to figure out a way for the Duplicate value to NOT appear in column D when there is an L in column E.

Let me know if I can clarify.

[newdoverman]

Enter in D2 and fill down:

Please Login or Register  to view this content.

Maybe even this if there are conditions that I don't know about. This checks that column D would return TRUE AND there is an L in column E. This might not be necessary though.

Please Login or Register  to view this content.

[morerockin]

I cannot get these to work . . duplicates are now showing up in places where duplicates are not supposed to be. But I realize I did a poor job of explaining the issue.

This pic should help.

2015-02-05 17_36_23-Microsoft Excel - Book2.xlsx.png

See the example workbook. Book2.xlsx

I need the formula to identify duplicates in Column C for each project grouping (column A) by determining if there are 2 or more of the same product ID that has NOT been marked for deletion. I apologize in advance for this request. If anyone is in the TN area, I'll take you out for a frosty bier.

[newdoverman]

These formulae I have tested in your new sample workbook and the appear to work. These formula include the data in column B:

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

This looks like an expanding list of data so I converted the list to a table. This allows the formulae to expand the ranges as you add data.

I have included both formulae in the example file.

[morerockin]

Newdoverman, I revised my last post while you posted a new one. I realize I did a poor job of explaining the issue.

This pic should help.

2015-02-05 17_36_23-Microsoft Excel - Book2.xlsx.png

See the example workbook.Book2.xlsx

I need the formula to identify duplicates in Column C for each project grouping (column A) by determining if there are 2 or more of the same product ID that has NOT been marked for deletion. I apologize in advance for this request. If anyone is in the TN area, I'll take you out for a frosty bier.

[newdoverman]

I have copied your new data into the last workbook that I uploaded. As I understand the problem, the workbook is working properly. All items marked with an L have the "TRUE" removed as per your earlier request.

I must admit that I am really confused as to what you are wanting. All the TRUE values in D are duplicates. I have made it so that if there is a TRUE and an L is entered in Column E the TRUE is taken out as you instructed in message 9.

[morerockin]

I have copied your new data into the last workbook that I uploaded. As I understand the problem, the workbook is working properly. All items marked with an L have the "TRUE" removed as per your earlier request.

I must admit that I am really confused as to what you are wanting. All the TRUE values in D are duplicates. I have made it so that if there is a TRUE and an L is entered in Column E the TRUE is taken out as you instructed in message 9.

Hello newdoverman,

I realize I am not communicating my issue well, so here is another picture.

2015-02-05 21_44_56-Microsoft Excel - Book2 - ndmV2.xlsx.png

My reason for making this request is because it is not a true duplicate if in our system if it has been deleted per the "L" in column E. I have to have a way to scrub for true duplicates.

I am so sorry that I am a bonehead!

[newdoverman]

I really don't understand why you need 2 deleted after L applied to other deleted in order to mark deleted but this seems to do that.

[morerockin]

Internet+High+Five.+Join+the+high-fivers.+Thumbs_4a7b41_3281983.jpg

SOLVED!!!!!!!!!!!!!!!!!! THANKS Newdoverman!!

[newdoverman]

LOL. Thanks for the feedback.

I hope that this solution works out for you. This has to be one of the stranger problems that I have worked on.

[morerockin]

Hey guys,

I have another pesky request.

In addition to my previous posts about finding a duplicate value based only on non deletion values, I have a new caveat to introduce.

See attached: Book2 - ndmV3 (1).xlsx

I have added new rows to the last sheet that Newdoverman uplaoded. Rows 28-43. I now need Column F to return a "duplicate" if Column C contains more than one of the non deleted (look for deletion value in column E)product ID's found in column H.

Example:

2015-02-20 10_31_22-Microsoft Excel - Book2 - ndmV3 (1).xlsx.png

Any help is appreciated.

[newdoverman]

I'm getting "twisted up" in the criteria. Could you in column G give manually what you expect the results to be? The requirements for duplicate identification in column E are already complicated and your new requirement has me wondering exactly what is required.

[morerockin]

Does this help?

2015-02-20 11_12_35-Microsoft Excel - Book2 - ndmV3 (1).xlsx.png

[newdoverman]

I can't get even close to what you are indicating what you want. The criteria as I understand them are contradictory and just don't work together.

[morerockin]

I can't get even close to what you are indicating what you want. The criteria as I understand them are contradictory and just don't work together.

I have an idea. Could we do a formula in Column C the the following Product ID's to "Engineer Services" IF it matched "HYBRIDENGINE", "Engineer INST", or "ENG SVC 2". If we did that, the previous formula that you created for me (previous to today) should work, right?

See example.

2015-02-20 13_07_02-Microsoft Excel - Book2 - ndmV3 (1).xlsx.png

[morerockin]

Desperate bump

[newdoverman]

I inserted a column D and used a formula with SUBSTITUTE and substituted all the terms in column C that matched those terms in your message. I then amended the formulae in the columns that addressed column C and changed the references to column D and this is the result.

[morerockin]

Hey newdoverman,

That worked. Now I am having the issue of being able to point out the duplicates that do NOT have a deletion value in column G. I tried the formula that you gave me previously and works on my other sheets, but it will not work on this one.

See Attached Book1.xlsx

I need column F to return "Duplicate" IF column E has 2 or more duplicate values that do NOT have a duplicate value in column G. Column H is an example of what the final result should be.

I will be dumping this into a sheet that has 36,000 rows.

[newdoverman]

This appears to be what you require in F2 and filled down:

Please Login or Register  to view this content.

The red text was what I found to be missing and required for the formula to work. Without the text in red, the formula was looking for BLANK cells in column G.