# finding duplicates with conditional formatting

#### smcexcel

##### New Member
Hi All,

I am trying to spot recurring invoices, per-arranged by pivot table in columns. For example I list out daily transactions from July to Aug (60 columns) then I would have the vendor names listed in rows.

Now my question is if I want to quickly identify which vendors have recurring(duplicated amount) invoices. I would first select the vendor then click on conditional formatting and highlight duplicated value. But if I have over 2000 rows how can I quickly drag and autofill?? Is there a easier way or better formulas for this?

P.S I won't have a pre-identified value. I am just trying to spot recurring payments throughout different months for different vendors.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### arthurbr

##### Well-known Member
Just select the entire range ( all transcations and vendors at once) and use the CF as you described.

#### smcexcel

##### New Member
Just select the entire range ( all transcations and vendors at once) and use the CF as you described.
Hi, the issue with that is, they will compare the numbers both horizontally and vertically. But I only want to compare the info from the same vendor.

#### CA_Punit

##### Well-known Member
You can use incremental Countif also. For example

Book1
BC
4INVOICECount if
5INA20090041
6INA20090061
7INA20090042
8INA20090043
9INA20090051
10INA20090062
11INA20090071
12INA20090081
13INA20090091
Sheet2
Cell Formulas
RangeFormula
C5:C13C5=COUNTIF(\$B\$4:B5,B5)

Here if the Countif is more than 1 then it is a duplicate entry.

#### smcexcel

##### New Member

You can use incremental Countif also. For example

Book1
BC
4INVOICECount if
5INA20090041
6INA20090061
7INA20090042
8INA20090043
9INA20090051
10INA20090062
11INA20090071
12INA20090081
13INA20090091
Sheet2
Cell Formulas
RangeFormula
C5:C13C5=COUNTIF(\$B\$4:B5,B5)

Here if the Countif is more than 1 then it is a duplicate entry.
Hi,

The above won't work for me as well. Because it requires a pre-identifier (invoice number). When I am analyzing the data I wouldn't know which item will be a recurring (duplicated value).

#### blbat

##### Active Member
if you want to Conditional format for Duplicates you could use:
Code:
``=COUNTIF(\$A\$2:\$B\$272,A2)>1``
then choose a Fill Color to highlight the duplicates.

(you can change that Range to whatever Range you're using.)

#### Fluff

##### MrExcel MVP, Moderator

Cross posted Best formulas/method for spotting recurring payment(values) from different months

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

#### CA_Punit

##### Well-known Member
I have a question let say Vendor A have issued invoice 1 in 04/07 for Rs 100 and Nil for 05/07 but Again 100 in 06/07 with invoice No 2
Will it be considered as Duplicate invoice

#### smcexcel

##### New Member
if you want to Conditional format for Duplicates you could use:
Code:
``=COUNTIF(\$A\$2:\$B\$272,A2)>1``
then choose a Fill Color to highlight the duplicates.

(you can change that Range to whatever Range you're using.)
Hi Blbat.

If I am to expand the analysis to invoice amount as below format. Can you help explain how it would work?

I sorted below data first using vendor name then amount then to posting date.

As you can see from below this vendor issued three payments, once in each month from April to Jun 2020 in the same amount with different invoice number. I supposed instead of counting invoice number i would be counting the amount.

But this is only one vendor with small amount of transactions what if I have vendors which have thousands of transactions within the quarter and I want to quickly identify the recurring payments just like the one shown below?
 Doc Date Posting Date invoice number Vendor name Account numbber Amount
 3/31/2020​ 4/20/2020​ 116791 SM0012001 647200 -24438.8​ 4/30/2020​ 5/31/2020​ 117318 SM0012001 647200 -24438.8​ 5/31/2020​ 6/22/2020​ 117881 SM0012001 647200 -24438.8​

#### smcexcel

##### New Member
I have a question let say Vendor A have issued invoice 1 in 04/07 for Rs 100 and Nil for 05/07 but Again 100 in 06/07 with invoice No 2
Will it be considered as Duplicate invoice
Yes. As long as they have a duplicated amount I would like to flag it down.

Replies
0
Views
94
Replies
9
Views
59
Replies
5
Views
64
Replies
18
Views
184
Replies
3
Views
36