• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To find Duplicate Value to highlight the 2nd duplicate value not both

R

rom

New Member
Need an urgent help,i am strugglin with a simple objective but don't know how to make excel work & get that result. I have to make an excel tracker for invoices recieved which will help us to track the number of duplicate invoices recieved.What i'd done is to combine 4 unique headers(=invoice number & invoice amount &Operating company & Vendor name) in the invoice & combine it to get a single unique value. now my problem is since the data is soo huge( more than 100,000 lines) i need a macro which will run the moment unique code is updated & check that range to see whether the unique value created appears twice or not, if yes then i want Value "1" to appear on the cell next to the unique value showing for the 2nd time. for eg: the value abc123 is appearing in "J1" & "J2", i want the Value "1" to show in "K2". Please help

The link for a sample work book is here http://www.4shared.com/file/MJdi7Urg/Workbook.html
 
You do not need a macro for this.

Assuming your data starts from 2nd row, and column J has the invoice unique identifier (=invoice number & invoice amount &Operating company & Vendor name)


In K2 write

=countif($J$2:J2,J2)>1


This will be TRUE for second, third etc. duplicates, but will be FALSE for first occurrence. You can then filter the list for TRUE values and either delete them or deal with them.
 
Thank you chandoo for your help, i am going to incorporate this into my tracker & see how it works :) hopefully i'll not goof it up


Btw do you still take Donuts? :D
 
You must log in or register to reply here.
Back
Top