Controller Posted January 3, 2008 Share Posted January 3, 2008 Excel help please: I need to find a way to identify cells within a spreadsheet that contain the same values (could be amounts or invoice numbers). Here is the application: I will have a sheet with hundreds or thousands of lines of data from invoices paid to a certain vendor. We suspect that this vendor has double-billed us for the same transactions, either using the same or slightly different invoice numbers. The amounts would be the same even the invoice numbers are not. Any ideas on how to spot duplicate values throughout a spreadsheet? Conditional formatting? If/then statement? Macro? Quote Link to comment Share on other sites More sharing options...
cre8tiff Posted January 3, 2008 Share Posted January 3, 2008 Excel help please: I need to find a way to identify cells within a spreadsheet that contain the same values (could be amounts or invoice numbers). Here is the application: I will have a sheet with hundreds or thousands of lines of data from invoices paid to a certain vendor. We suspect that this vendor has double-billed us for the same transactions, either using the same or slightly different invoice numbers. The amounts would be the same even the invoice numbers are not. Any ideas on how to spot duplicate values throughout a spreadsheet? Conditional formatting? If/then statement? Macro? Data sort on the value column would list all duplicate values together. Anything more than that I always just import it into Access and run queries. Heckuva lot simpler. Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 3, 2008 Share Posted January 3, 2008 Could always run a pivot table to do a count by invoice number, that would quickly identify if there are any duplicates in that column. Could do the same on the amounts column? Once you do the pivot table, you can double click on any of the "values and it will create a new sheet for you with just the records you wanted to drill down on. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted January 4, 2008 Share Posted January 4, 2008 Very simple and comes in handy a lot. Follow these steps.. Sort the data that you want to find duplicates (let's assume column A). In the next column (Column to the right put a zero next to the first piece of data. Assume Cell B1 In the cell below it (B2) enter this simple formula =if(B2=B1,1,0). Copy this cell all the way to the end of your data. Any cell that has a one in it is a duplicate of the item above it. Subtotal that column and you will know how many duplicates you have. To get all the duplicates in a list do the following: Copy and paste special vlaues column B into Column C (or over the top of Column B if you don't mind losing the original formulas) Sort your data based on this new column of data in Descending order and you will have all of the cells with a one in it at the top which are your duplicates. It may sound time consuming but once you learn this it takes about 30 seconds and comes in very handy. Quote Link to comment Share on other sites More sharing options...
Controller Posted January 5, 2008 Author Share Posted January 5, 2008 Thanks to all - very helpful. Quote Link to comment Share on other sites More sharing options...
dmarc117 Posted January 5, 2008 Share Posted January 5, 2008 Thanks to all - very helpful. well....were they f'ing ya? Quote Link to comment Share on other sites More sharing options...
Thews40 Posted January 5, 2008 Share Posted January 5, 2008 lookup tables work great, but are a royal pain to set up. I've done it before, but it's been a while. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.