finding duplicates with conditional formatting

[smcexcel]

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....

 

[arthurbr]

Just select the entire range ( all transcations and vendors at once) and use the CF as you described.

 

[smcexcel]

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]

You can use incremental Countif also. For example

Book1
	B	C
4	INVOICE	Count if
5	INA2009004	1
6	INA2009006	1
7	INA2009004	2
8	INA2009004	3
9	INA2009005	1
10	INA2009006	2
11	INA2009007	1
12	INA2009008	1
13	INA2009009	1
Sheet2

Cell Formulas
Range	Formula
C5:C13	C5	=COUNTIF($B$4:B5,B5)

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

 

[smcexcel]

You can use incremental Countif also. For example

Book1
	B	C
4	INVOICE	Count if
5	INA2009004	1
6	INA2009006	1
7	INA2009004	2
8	INA2009004	3
9	INA2009005	1
10	INA2009006	2
11	INA2009007	1
12	INA2009008	1
13	INA2009009	1
Sheet2

Cell Formulas
Range	Formula
C5:C13	C5	=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]

if you want to Conditional format for Duplicates you could use:

=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]

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]

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]

if you want to Conditional format for Duplicates you could use:

=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]

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.