Sorting and cataloguing duplicate values on older PCs

[cowmu]

I will do my best to outline my situation. For my job, we receive a sets of data which require our team to reach out and contact people. As new data sets arrive, there is a good chance that some of these values are going to be duplicates. At present, I do Conditional Formatting along with sorting to locate duplicate values in Excel. The biggest issue I am having is simply due to the time it takes to complete this task. The manual effort is minimal, but it can take nearly 30 minutes for some of the larger sets for the sort to complete. Additionally, this method requires the user to repeat the conditional formatting over various spreadsheets.

I am looking for suggestions on how I could create a Workbook or Database that can essentially be used to determine the number of times a record is duplicated. Ideally, I would prefer to have one Excel Workbook or Access Database that could host every single person that we encounter in a data element, and store it in a "master" file. Each time a new data set is provided, the user could add or modify the master to contain new values, or to indicate the additional data set that the duplicate value resides in. I am reasonably well versed in Excel, but I have not had to use Access since high school. I feel I could pick it up pretty quickly if needed.

I would appreciate any suggestions or feedback offered.

[:) Sixthsense :)]

My first suggestion is don't use Conditional Formatting to determine the duplicate value. Instead use a helper column for identifying the data.

Use the new arrival of Remove Duplicates in 2007 (Data>>Remove Duplicates). But in the testing stage do it in a duplicate workbook.

[FDibbins]

an alternative option would be to use countif() to ID duplicates, and to give the 1st instance of every value a sequential number. You could then use index/match to create a new, unique, table based on those sequential numbers. every time you get new data, it can just be added to the end and the table should update itself

[Jarko28]

hi,

why not just use a pivot table?

Create a dynamic range for the data: on the ribbon >>>Formulas>>>Name Manager


Choose a name let's say "Customers"...and in the "Refers to" , put in something like:

Please Login or Register  to view this content.

this will handle rows being added and columns static at 5 in this example for a range starting in A1 on Sheet1...

The create a pivot table with the data range "=Customers"

then drag customer names in rows and in the values, do the same and choose "count of" values....

[cowmu]

I have sort of concluded that conditional formatting is the clear issue that is taxing the processor. I have seen/used the removed duplicate, but that is more or less the opposite of my intentions. My end goal is to be able to have one resource (Excel/Access file) that contains every single person over my 6 or 7 spreadsheets and identifies and duplicates. The remove duplicate function was extremely quick though, but unless there is way to indicate which files are being removed, it does not seem like an acceptable possibility.

I am not extremely familiar with the CountIf function. The spreadsheets that I am using will be using tens of thousands of records each, and the duplicates of all the data are in the thousands range, and the identifiers are all numeric. Would it be possible to start a numeric sequence at a certain point following the final numeric character and go from there? I am a bit confused on that, but if I have time to play around with this at work tomorrow, I will probably have a more clear idea as to what you are talking about. I will also definitely give pivot tables a try, but as with the CountIf function, it is not something I have had any experience in. It does not sound very complex though. I appreciate all the help!