table.JPG

Hello

I am using Excel 2010. (see attachment showing the sheet).

I have a large "master" sheet with 40 columns and approx 5000 rows, which is the result of combining many same-format sheets by many different users, in different locations. It's messy methodology, but at the moment this process is unchangeable.

Column A of each user's sheet would (originally) contain the unique identifier for each row, plus relevant data in the other 39 columns.

By the time I get the get the combined master version, there are many instances of the "unique" column A identifier. Not just duplicates, but anything up to 6 entries for the same entity (plus the relevant data for each, in the rest of the 39 columns!)

I can highlight duplicates in Excel by conditional formatting, but I'm looking for a way to flag the duplicates with a number (eg 1,2,3,4,5 etc) - I could then export to MS Access and combine them properly using SQL.

Here's a sample of before and after, with the flags added - the way it is now, and what it should look like afterwards (much simplified, of course!) :

I hope I gave given enough info and presented it clearly.

Any help would be greatly appreciated!

Thanks