excel find duplicates 1

[Cpreston]

Hi

I am trying to din a way to compere 2 columns for duplicates at the same time

Product Custcode
12345 1
12333 2
12444 1
12345 1
12467 2
12345 2

I only want it to show if the Product and the custcode are the same (or highlight it in some way some way). In the example above I would expect to see that 12345 and 1 are duplicated twice

12345 1
12345 1

I cannot find a way to do this at all, could someone help please.

Thanks

 

[SkipVought]

Hi,

It could be done with MS Query, joining the two tables on both columns, assuming the two tables are on two different sheets.

Or you can add a helper column to each table where you concatenate the values in the two columns. Then use the MATCH() function in yet another column on each row to lookup the concatenated value in the other table's concatenated value colum range. Do that on both tables.

 

[mintjulep]

Conditional Formatting has a highlight duplicates feature built-in.

Search Excel help for "find duplicates

 

[kjv1611]

If you just need to see the dups, then I think the conditional formatting will work best. To use conditional formatting to check values of 2 fields, you'll need to use a formula within the conditional formatting. It gets a bit more tricky than just selecting conditional formatting, but it's definitely doable.

Then if you ONLY want to show the duplicates, you can filter by color, assuming formatting doesn't matter for the end recipient.

If the end presentation matters more, coloring/formatting wise, then the MS Query option could end up being your best or using INDEX and MATCH the way Skip mentioned.

I guess a "cheat" way of getting around needing MS Query and/or formulas and still maintain "clean" formatting in the end, would be to make sure there is no fill on most cells, and no table formatting, then use White for the fill color on duplicates, and maybe also uncheck gridlines on the View tab.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Cpreston]

Hi

Thanks for the replys but I cannot seem to figure it out.

The information is all on one sheet to start with and the two columns I need to compare are Product and Customercode

I only want to see the ones that are duplicate so in my example below I would expect to see. I cant figure out how the Match function would work like this and using conditional formatting does not appear to work. Could someone give me a step by step way to do it, I appreciate this is not a tutorial site but I have tried everything and cannot get the result I need, thanks in advance

12345 1
12345 1


Product Custcode
12345 1
12333 2
12444 1
12345 1
12467 2
12345 2

I only want it to show if the Product and the custcode are the same (or highlight it in some way some way). In the example above I would expect to see that 12345 and 1 are duplicated twice

12345 1
12345 1

 

[kjv1611]

Sounds like you're just working on something, not presenting... so here's one easy option, I think, to use the conditional formatting...
[ol 1]
[li]Create a new column - stick in in Column A, for instance, or either in Column C - whatever you want. I'll assume your current data is in A and B, and you'll insert this new column into A... so then A and B become B and C.[/li]
[li]Assuming the first row of data is Row 2, put this formula in Cell A2:

=CONCATENATE(B2,C2)

[/li]
[li]Then select all your data cells in Column A (select A2, press [Ctrl] + [Shift] + [Down Arrow] on keyboard.[/li]
[li]On Home Tab in Excel 2010, select Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.[/li]
[li]Change the color/formatting in the drop-down to the right, then click OK[/li]
[li]Then if you want to sort the dups to the top, select the entire range (use [Ctrl} + [Shift] + [keyboard 8] or [Ctrl] + [Numpad *]) and go to Sort and Filter -> Custom Sort -> "Sort" button if you have to -> Then Sort by your new field - whatever you called it in cell A1... -> Sort on Cell Color -> Select whatever fill color you selected earlier for the dup values. Then I'd add a second sort option below (click "Add Level" in Sort window, and sort the same column by value.[/li]
[/ol]


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Cpreston]

Yes that worked great kjv1611 thanks for the help and to all the replys