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

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Book1
BCDEF
20Vendor01-Aug02-Aug03-Aug04-Aug
21x100200100150
22y1000200200
23z
24
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21:F22Expression=COUNTIF($C21:$F21,C21)>1textNO
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

smcexcel

New Member
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Book1
BCDEF
20Vendor01-Aug02-Aug03-Aug04-Aug
21x100200100150
22y1000200200
23z
24
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21:F22Expression=COUNTIF($C21:$F21,C21)>1textNO
Thanks, CA_Punit.

Question, in your above conditional formatting. You have an identifier "C21" for your row 21.

For row 22, do you have to change the identifier again?

Secondly how do you bring up the second interface"Cells with conditional Formatting? For me when I add new rules. it has

below selection.

1597173691513.png
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Select Use a formula to determine which cell to format

So Step 1: Select Whole Range From C1 to K100(Say) where Invoice value Lie

The Formula Countif($C1:$K1,C1)>1 (The ranges are imaginary) (Modify it) put in "Use a formula to determine which cell to format" while selecting the range C1:K100

and Format the cell with colour and Press Enter
 

Watch MrExcel Video

Forum statistics

Threads
1,114,665
Messages
5,549,305
Members
410,908
Latest member
Allen P
Top