finding duplicates with conditional formatting

smcexcel

New Member
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Thank you for reading this.

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.

Please help....
 

Some videos you may like

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
Joined
Dec 8, 2006
Messages
2,115
Office Version
  1. 2010
Just select the entire range ( all transcations and vendors at once) and use the CF as you described.
 

smcexcel

New Member
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 24, 2010
Messages
292
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
Joined
Jun 12, 2014
Messages
48,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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 DatePosting Dateinvoice numberVendor nameAccount numbberAmount
3/31/2020​
4/20/2020​
116791SM0012001647200
-24438.8​
4/30/2020​
5/31/2020​
117318SM0012001647200
-24438.8​
5/31/2020​
6/22/2020​
117881SM0012001647200
-24438.8​
 

smcexcel

New Member
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,064
Messages
5,545,784
Members
410,706
Latest member
antstt
Top