best way to identify duplicates

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
93
Office Version
  1. 365
  2. 2016
So me and my co-workers share a monthly excel file for keeping track of customers bills.

I converted this file into a table.

In some occasions, we found that the bill number (column A) appears more than once.
The number is a 12-length character but I see that some of my colleagues do copy/paste from emails without realizing that they paste the bill number with spaces.


What is the best way to highlight these duplicates (even with spaces)?

I created a Macro button to show the bills due in the current date.

I suppose I could include VBA code to show the duplicates as well but there might be issues if the duplicates are inserted with different dates (if that happens, there was a mistake when inserting it as bill number is unique)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
@excel01noob When you say that they copy/paste with spaces, is that just leading and/or trailing spaces?
If so then does conditional formatting help?

Book1
A
1Order Num
2123456789ABC
3234567891ABC
4 123456789ABC
5 123456789ABC
6
7
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A6Expression=((A2<>"")*COUNTIF(A:A,"*"&TRIM(A2)&"*"))>1textNO
 
Upvote 0
Hi Snakehips

yes, leading and/or trailing spaces all the time I found the duplicates
That helps but I could not produce the coloring on both the duplicated cells
 
Upvote 0
I converted this file into a table.
In that case could you use Conditional formatting like this?
Select the Bill Number column (excluding heading) in your table
Apply the Conditional Formatting shown below, remembering to
- use the first selected cell address where I have A2,
- amend the table name if required
- Amend the Bill Number table column heading if required.

This CF should automatically expand/contract with changes to the table.

excel01noob 1.xlsm
AB
1Bill NumberCustomer
2123456789ABCCust 1
3234567891ABCCust 2
4123456789ABCCust 3
5123456789ABCCust 4
6
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A5Expression=COUNT(SEARCH(TRIM(A2),INDIRECT("Table1[Bill Number]")))>1textNO
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top